All 8 messages in topic - view as tree
From: mc - view profile
Date: Tues, Dec 5 2006 11:31 am
Email: "mc"
Hi,
I need to insert all the dates in 2007-2009 into a table in the
following format.
calendar_date 20070101
Day_of_week MON
The column "calendar_dt" is not of type date, but character.
Reply » Rate this post: Text for clearing space
Subject changed: To insert Dates in a particular format (with day of the week)
From: Obnoxio The Clown - view profile
Date: Tues, Dec 5 2006 11:55 am
Email: "Obnoxio The Clown"
mc said:
> Hi,
> I need to insert all the dates in 2007-2009 into a table in the
> following format.
> calendar_date 20070101
> Day_of_week MON
> The column "calendar_dt" is not of type date, but character.
Have you considered using the INSERT statement?
--
Bye now,
Obnoxio
"I don't read newspapers anymore except the local rag which I do weekly to
cheer myself trying to see if anyone I hate has been stabbed."
-- Horribilis XVI
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
Reply » Rate this post: Text for clearing space
Subject changed: To insert Dates in a particular format (with day of the week) into a table.
From: Superboer - view profile
Date: Tues, Dec 5 2006 12:15 pm
Email: "Superboer"
export DBDATE=Y4MD0
an spl something like
create procedure superboer()
define nrdays int;
define startday date;
define daystring char(3)
let startday = "20070101";
let nrdays = 700;
let daystring="";
while (nrdays > 0 )
let nrdays = nrdays -1;
let startday = startday + 1 units day;
if(weekday(startday) = 0 ) THEN
let daystring = "SUN"
END IF
if(weekday(startday) = 1 ) THEN
let daystring = "MON"
END IF
.........
insert into sometable values(startday,daystring);
END WHILE
end procedure
Superboer.
WARNING NOT TESTED!!!!!!!!!
mc schreef:
- Hide quoted text -
- Show quoted text -
> Hi,
> I need to insert all the dates in 2007-2009 into a table in the
> following format.
> calendar_date 20070101
> Day_of_week MON
> The column "calendar_dt" is not of type date, but character.
Reply » Rate this post: Text for clearing space
From: Doug Lawry - view profile
Date: Tues, Dec 5 2006 12:23 pm
Email: "Doug Lawry"
A solution follows. The only question is "Why?" :-)
--
Regards,
Doug Lawry
www.douglawry.webhop.org
CREATE TABLE calendar
(
calendar_date CHAR(8) NOT NULL UNIQUE,
day_of_week CHAR(3) NOT NULL
);
CREATE PROCEDURE fill_calendar
(
p_min_year SMALLINT,
p_max_year SMALLINT
)
DEFINE l_date DATE;
LET l_date = MDY(1, 1, p_min_year);
WHILE YEAR(l_date) <= p_max_year
INSERT INTO calendar VALUES
(
TO_CHAR(l_date, '%Y%m%d'),
UPPER(TO_CHAR(l_date, '%a'))
);
LET l_date = l_date + 1;
END WHILE
END PROCEDURE;
EXECUTE PROCEDURE fill_calendar(2007, 2009);
"mc"
news:1165311084.688336.218120@j72g2000cwa.googlegroups.com...
- Hide quoted text -
- Show quoted text -
> Hi,
> I need to insert all the dates in 2007-2009 into a table in the
> following format.
> calendar_date 20070101
> Day_of_week MON
> The column "calendar_dt" is not of type date, but character.
Reply » Rate this post: Text for clearing space
From: Carsten Haese - view profile
Date: Tues, Dec 5 2006 4:41 pm
Email: Carsten Haese
On Tue, 2006-12-05 at 10:23 +0000, Doug Lawry wrote:
> A solution follows. The only question is "Why?" :-)
Maybe he (or she?) is trying to get featured on thedailywtf.com...
And just for fun, here's a solution in Python:
#================================================================
def gen_data():
import datetime
date = datetime.date(2007,1,1)
end_date = datetime.date(2009,12,31)
while date <= end_date:
yield (date.strftime("%Y%m%d"), date.strftime("%a").upper())
date += datetime.timedelta(days=1)
import informixdb
conn = informixdb.connect("stores_demo")
cur = conn.cursor()
cur.execute("""
create table calendar (
calendar_date char(8),
day_of_week char(3)
)
""")
cur.executemany("insert into calendar values(?,?)", gen_data())
conn.commit()
#================================================================
Maybe I will discuss this solution during my WAIUG Forum presentation as
a real-world example. :)
-Carsten
Reply » Rate this post: Text for clearing space
From: Art S. Kagel - view profile
Date: Tues, Dec 5 2006 4:58 pm
Email: "Art S. Kagel"
mc wrote:
> Hi,
> I need to insert all the dates in 2007-2009 into a table in the
> following format.
> calendar_date 20070101
> Day_of_week MON
> The column "calendar_dt" is not of type date, but character.
WHY??????
IDS provides the weekday(
day of the week of any date as an integer without using any storage. A
simple CASE statement can translate the day number into a string. Why build
such a permanent table? If you have a need for a particular query to
repeatedly convert the same date you can always build a quick temp table...
OK, so you still need to know how to do that...
What do you want to use for a front-end? To use SPL, I'd take a simple
approach and take advantage of the engine's ability to convert types:
create procedure add_dow( start, end );
define start, end integer;
define dt date;
define thisdate, yr, mo, dy, dow integer;
define dow char(3);
for thisdate = start to end
let yr = thisdate / 10000;
let mo = mod((thisdate / 100), 100);
let dy = mod( thisdate, 100);
let dt = mdy( mo, dy, yr );
let dow = weekday( dt );
if (dow = 0) then let day = 'SUN';
else if (dow = 1) then let day = 'MON';
else if (dow = 2) then let day = 'TUE';
else if (dow = 3) then let day = 'WED';
else if (dow = 4) then let day = 'THU';
else if (dow = 5) then let day = 'FRI';
else if (dow = 6) then let day = 'SAT';
end if;
insert into datetable values( dt, day )
end for;
end procedure;
-- Not tested.
Art S. Kagel
Reply » Rate this post: Text for clearing space
From: bozon - view profile
Date: Wed, Dec 6 2006 5:10 pm
Email: "bozon"
Building on Art's fine answer if you need to do this, then the database
as the to_char function built in:
select to_char(current, "%a") from systables where tabid = 1;
select to_char(current, "%A") from systables where tabid = 1;
So the database already does this for you and the nice thing is that
you will get the answer in the current locale so now you have an
international product. ;-) If you have to build a table then use the
same function to get the results that you need
Here is the excerpt from the FM, since you probably don't have access
to one. ;-)
SELECT TO_CHAR(begin_date, '%A %B %d, %Y %R') FROM tab1
The symbols in the format_string parameter in this example have the
following meanings. For a complete list of format symbols and their
meanings, see the GL_DATE and GL_DATETIME environment variables in the
IBM Informix: GLS User's Guide. Symbol Meaning %A Full weekday name as
defined in the locale %B Full month name as defined in the locale %d
Day of the month as a decimal number %Y Year as a 4-digit decimal
number %R Time in 24-hour notation The result of applying the specified
format_string to the begin_date column is as follows: Wednesday July
23, 1997 18:45 TO_DATE Function (IDS): The TO_DATE function converts a
character string to a DATETIME value. The function evaluates the
char_expression parameter as a date according to the date format you
specify in the format_string parameter and returns the equivalent date.
If char_expression is NULL, then a NULL value is returned. Any argument
to the TO_DATE function must be of a built-in data type. If you omit
the format_string parameter, the TO_DATE function applies the default
DATETIME format to the DATETIME value. The default DATETIME format is
specified by the GL_DATETIME environment variable.
Here is the link for the manuals in case you have access to the
internet:
http://www-306.ibm.com/software/data/informix/pubs/library/
I find the SQL reference manual to be very helpful in these situations.
- Hide quoted text -
- Show quoted text -
mc wrote:
> Hi,
> I need to insert all the dates in 2007-2009 into a table in the
> following format.
> calendar_date 20070101
> Day_of_week MON
> The column "calendar_dt" is not of type date, but character.
Reply » Rate this post: Text for clearing space
From: bozon - view profile
Date: Thurs, Dec 7 2006 3:12 am
Email: "bozon"
Ooops my answer isn't quite right.
> select to_char(current, "%a") from systables where tabid = 1;
> select to_char(current, "%A") from systables where tabid = 1;
select upper(to_char(current, "%a")) from systables where tabid = 1;
select upper(to_char(current, "%A")) from systables where tabid = 1;
I hope this cause any problems. ;-)
- Hide quoted text -
- Show quoted text -
bozon wrote:
> Building on Art's fine answer if you need to do this, then the database
> as the to_char function built in:
> select to_char(current, "%a") from systables where tabid = 1;
> select to_char(current, "%A") from systables where tabid = 1;
> So the database already does this for you and the nice thing is that
> you will get the answer in the current locale so now you have an
> international product. ;-) If you have to build a table then use the
> same function to get the results that you need
> Here is the excerpt from the FM, since you probably don't have access
> to one. ;-)
> SELECT TO_CHAR(begin_date, '%A %B %d, %Y %R') FROM tab1
> The symbols in the format_string parameter in this example have the
> following meanings. For a complete list of format symbols and their
> meanings, see the GL_DATE and GL_DATETIME environment variables in the
> IBM Informix: GLS User's Guide. Symbol Meaning %A Full weekday name as
> defined in the locale %B Full month name as defined in the locale %d
> Day of the month as a decimal number %Y Year as a 4-digit decimal
> number %R Time in 24-hour notation The result of applying the specified
> format_string to the begin_date column is as follows: Wednesday July
> 23, 1997 18:45 TO_DATE Function (IDS): The TO_DATE function converts a
> character string to a DATETIME value. The function evaluates the
> char_expression parameter as a date according to the date format you
> specify in the format_string parameter and returns the equivalent date.
> If char_expression is NULL, then a NULL value is returned. Any argument
> to the TO_DATE function must be of a built-in data type. If you omit
> the format_string parameter, the TO_DATE function applies the default
> DATETIME format to the DATETIME value. The default DATETIME format is
> specified by the GL_DATETIME environment variable.
> Here is the link for the manuals in case you have access to the
> internet:
> http://www-306.ibm.com/software/data/informix/pubs/library/
> I find the SQL reference manual to be very helpful in these situations.
> mc wrote:
> > Hi,
> > I need to insert all the dates in 2007-2009 into a table in the
> > following format.
> > calendar_date 20070101
> > Day_of_week MON
> > The column "calendar_dt" is not of type date, but character.
Reply » Rate this post: Text for clearing space
End of messages
No comments:
Post a Comment