To insert Dates in a particular format (with day of the week) into a table.
A poster asked how to populate a table with every date from 2007-2009 stored as a CHAR string like '20070101' plus a three-letter day-of-week column ('MON'). Several working answers were given: stored procedures (SPL) that loop a DATE variable one day at a time, using WEEKDAY() with IF/CASE logic to map 0-6 to day names, or Doug Lawry's simpler version using TO_CHAR(date,'%Y%m%d') and UPPER(TO_CHAR(date,'%a')); Carsten Haese offered an equivalent Python/informixdb script. Others questioned the need for such a table at all, since WEEKDAY() can derive the day on the fly.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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.
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.
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:
> 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.
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" <moncyp@gmail.com> wrote in message
news:1165311084.688336.218120@j72g2000cwa.googlegroups.com...
> 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.
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
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( <date or datetime> ) function to return you the
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