Re: select time or date value from a date field?

Posted in 2008

Summary

Poster wanted to split a DATETIME column into separate date and time values. Art Kagel suggested EXTEND(create_dt, YEAR TO DAY) and EXTEND(create_dt, HOUR TO MINUTE); the date part worked but the time part always returned 12-00. Suggestions were that the column's precision was too coarse (e.g. DATETIME YEAR TO DAY/HOUR), but the poster confirmed it was DATETIME YEAR TO SECOND. Art posted a working test case showing EXTEND correctly returning hour/minute, concluding the problem was local to the poster's environment/tool. No root cause or fix is recorded; the poster simply said he would check on his end.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

vchu via DBMonster.com — Mon, 24 Mar 2008 18:40:06 GMT — source: Usenet: comp.databases.informix

it works for extend( create_dt, YEAR TO DAY) , but not for extend( create_dt, HOUR TO MINUTE). extend( create_dt, HOUR TO MINUTE) always return 12-00 am i missing something? Art S. Kagel (Oninit) wrote: >> do we have something like >> >[quoted text clipped - 5 lines] >> >> >SELECT extend( create_dt, YEAR TO DAY) as Create_Date, > extend( create_dt, HOUR TO MINUTE) as Create_Time >FROM anInformixTable ...; > >Assuming of course that the table has a column named create_dt... > >Art S. Kagel >Oninit > >=========================================================================================== >Please access the attached hyperlink for an important electronic communications disclaimer: > >http://www.oninit.com/home/disclaimer.php > >=========================================================================================== -- Message posted via http://www.dbmonster.com

↪ replying to vchu via DBMonster.com

Everett Mills — Mon, 24 Mar 2008 13:57:03 -0500 — source: Usenet: comp.databases.informix

> -----Original Message----- > From: informix-list-bounces@iiug.org [mailto:informix-list- > bounces@iiug.org] On Behalf Of vchu via DBMonster.com > Sent: Monday, March 24, 2008 1:40 PM > To: informix-list@iiug.org > Subject: Re: select time or date value from a date field? > > it works for extend( create_dt, YEAR TO DAY) , but not for extend( > create_dt, > HOUR TO MINUTE). What is the data type of create_dt? If it's DATE, you can only extract month, day and year from it. If it's DATETIME, then it needs to include hour and minute to be able to extract them: DATETIME YEAR TO SECOND contains hour and minute, DATETIME YEAR TO DAY doesn't. --EEM > > extend( create_dt, HOUR TO MINUTE) always return 12-00 > > am i missing something? > > > Art S. Kagel (Oninit) wrote: > >> do we have something like > >> > >[quoted text clipped - 5 lines] > >> > >> > >SELECT extend( create_dt, YEAR TO DAY) as Create_Date, > > extend( create_dt, HOUR TO MINUTE) as Create_Time > >FROM anInformixTable ...; > > > >Assuming of course that the table has a column named create_dt... > > > >Art S. Kagel > >Oninit > > > >======================================================================= == > ================== > >Please access the attached hyperlink for an important electronic > communications disclaimer: > > > >http://www.oninit.com/home/disclaimer.php > > > >======================================================================= == > ================== > > -- > Message posted via http://www.dbmonster.com > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

↪ replying to vchu via DBMonster.com

Art S. Kagel (Oninit) — Mon, 24 Mar 2008 15:38:23 -0400 — source: Usenet: comp.databases.informix

vchu via DBMonster.com wrote: > it works for extend( create_dt, YEAR TO DAY) , but not for extend( create_dt, > HOUR TO MINUTE). > > extend( create_dt, HOUR TO MINUTE) always return 12-00 > > am i missing something? > Sounds like the resolution of create_dt is DATETIME YEAR TO HOUR, so hours are the finest time you'll be able to extract from that. Art S. Kagel Oninit > > Art S. Kagel (Oninit) wrote: > >>> do we have something like >>> >>> >> [quoted text clipped - 5 lines] >> >>> >>> >> SELECT extend( create_dt, YEAR TO DAY) as Create_Date, >> extend( create_dt, HOUR TO MINUTE) as Create_Time >> > >FROM anInformixTable ...; > >> Assuming of course that the table has a column named create_dt... >> >> Art S. Kagel >> Oninit >> >> =========================================================================================== >> Please access the attached hyperlink for an important electronic communications disclaimer: >> >> http://www.oninit.com/home/disclaimer.php >> >> =========================================================================================== >> > >

↪ replying to Art S. Kagel (Oninit)

vchu via DBMonster.com — Tue, 25 Mar 2008 15:04:09 GMT — source: Usenet: comp.databases.informix

if I do select create_dt from contract_audit;it will return something like 2006-05-18 11:45:38 if i do Select extend(create_dt, YEAR TO DAY) from contract_audit, it will return 2006-05-18 but if i do select extend(create_dt, HOUR TO MINUTE). It always return 12-00. just check the data type of this field, it shows name = create_dt, type =DATETIME YEAR TO SECOND, length=19, Nulls =N Art S. Kagel (Oninit) wrote: >> it works for extend( create_dt, YEAR TO DAY) , but not for extend( create_dt, >> HOUR TO MINUTE). >[quoted text clipped - 3 lines] >> am i missing something? >> > >Sounds like the resolution of create_dt is DATETIME YEAR TO HOUR, so >hours are the finest time you'll be able to extract from that. > >Art S. Kagel >Oninit > >> >>>> do we have something like >[quoted text clipped - 21 lines] >>> =========================================================================================== >>> -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200803/1

↪ replying to vchu via DBMonster.com

Art S. Kagel (Oninit) — Tue, 25 Mar 2008 11:14:45 -0400 — source: Usenet: comp.databases.informix

vchu via DBMonster.com wrote: Something's wrong at your end. Works for me, I get: Database selected. > create table datecol(create_dt datetime year to second);Table created. > insert into datecol values (current);1 row(s) inserted. > insert into datecol values (current);1 row(s) inserted. > select * from datecol;create_dt 2008-03-25 11:07:34 2008-03-25 11:07:42 2 row(s) retrieved. > select extend(create_dt, year to day) from datecol; (expression) 2008-03-25 2008-03-25 2 row(s) retrieved. > select extend(create_dt, hour to minute) from datecol; (expression) 11:07 11:07 2 row(s) retrieved. > select extend(create_dt, hour to second) from datecol; (expression) 11:07:34 11:07:42 2 row(s) retrieved. > select extend(create_dt, minute to second) from datecol; (expression) 07:34 07:42 2 row(s) retrieved. Art S. Kagel Oninit > if I do > select create_dt from contract_audit;> it will return something like 2006-05-18 11:45:38 > > if i do > Select extend(create_dt, YEAR TO DAY) from contract_audit, > it will return 2006-05-18 > > but if i do > select extend(create_dt, HOUR TO MINUTE). > It always return 12-00. > > just check the data type of this field, it shows name = create_dt, type > =DATETIME YEAR TO SECOND, length=19, Nulls =N > > Art S. Kagel (Oninit) wrote: > >>> it works for extend( create_dt, YEAR TO DAY) , but not for extend( create_dt, >>> HOUR TO MINUTE). >>> >> [quoted text clipped - 3 lines] >> >>> am i missing something? >>> >>> >> Sounds like the resolution of create_dt is DATETIME YEAR TO HOUR, so >> hours are the finest time you'll be able to extract from that. >> >> Art S. Kagel >> Oninit >> >> >>> >>> >>>>> do we have something like >>>>> >> [quoted text clipped - 21 lines] >> >>>> =========================================================================================== >>>> >>>> > >

↪ replying to Art S. Kagel (Oninit)

vchu via DBMonster.com — Tue, 25 Mar 2008 15:32:23 GMT — source: Usenet: comp.databases.informix

thanks Art, I will check on my end. Art S. Kagel (Oninit) wrote: >Something's wrong at your end. Works for me, I get: > >Database selected. > > create table datecol(create_dt datetime year to second);>Table created. > > insert into datecol values (current);>1 row(s) inserted. > > insert into datecol values (current);>1 row(s) inserted. > > select * from datecol;>create_dt >2008-03-25 11:07:34 >2008-03-25 11:07:42 >2 row(s) retrieved. > > select extend(create_dt, year to day) from datecol; >(expression) >2008-03-25 >2008-03-25 >2 row(s) retrieved. > > select extend(create_dt, hour to minute) from datecol; >(expression) >11:07 >11:07 >2 row(s) retrieved. > > select extend(create_dt, hour to second) from datecol; >(expression) >11:07:34 >11:07:42 >2 row(s) retrieved. > > select extend(create_dt, minute to second) from datecol; >(expression) >07:34 >07:42 >2 row(s) retrieved. > >Art S. Kagel >Oninit > >> if I do >> select create_dt from contract_audit;>[quoted text clipped - 36 lines] >>>>> >>>>> -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/informix/200803/1