DateTime Conversion

Hey All, I'm an informix noob and I'm having some problems trying to UNION two tables due to some date/time fields. One table had a DateTime and the other table has Date, Time, Seconds columns. So, I believe that I either need to parse out the DateTime field or create a DateTime out of the Date, Time, Seconds columns. Thanks, James ==== Table A ==== last_update (DateTime) ==== Table B ==== activity_date (DATE) activity_time (SMALLINT) activity_seconds (SMALLINT) ==== Sample data from Table A ==== 2010-01-20 07:51:42 2010-01-20 09:35:02 2010-01-20 12:13:28 2010-01-21 07:19:56 ==== Sample data from Table B === 01/20/10 1924 2400 01/20/10 1518 3100 01/20/10 1515 2800 01/20/10 1440 200 01/20/10 1311 1100 01/20/10 1310 4000 01/20/10 1243 2700 01/20/10 1105 5100 01/20/10 1104 5800 01/20/10 938 4400 01/20/10 936 5100 01/20/10 936 300 01/20/10 935 4500 01/20/10 935 2500 01/20/10 807 1000 01/20/10 751 4200

Either way works. The method I'm showing depends on type casting which is first available in IDS 9.xx, so if you have 7.xx or earlier your'll have to do it differently. (A good argument for ALWAYS POSTING YOUR SERVER VERSION AND PLATFORM INFORMATION - don't you think?): select ..., last_update::DATE, (((extend(last_update, hour tohour)::CHAR(2))::INT * 100) + ((extend(last_update, minute to minute)::CHAR(2))::INT), second(last_update),... I'll leave the inverse conversion as an exercise with the advice that you'll have to assemble the DATETIME as a string from the date, hour/minute, and second columns. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jan 21, 2010 at 12:31 PM, J. Hart <unleashedmaniac@gmail.com> wrote: > Hey All, > > I'm an informix noob and I'm having some problems trying to UNION two > tables due to some date/time fields. > > One table had a DateTime and the other table has Date, Time, Seconds > columns. So, I believe that I either need to parse out the DateTime > field or create a DateTime out of the Date, Time, Seconds columns. > > Thanks, > > James > > ==== Table A ==== > last_update (DateTime) > > ==== Table B ==== > activity_date (DATE) > activity_time (SMALLINT) > activity_seconds (SMALLINT) > > > ==== Sample data from Table A ==== > 2010-01-20 07:51:42 > 2010-01-20 09:35:02 > 2010-01-20 12:13:28 > 2010-01-21 07:19:56 > > ==== Sample data from Table B === > 01/20/10 1924 2400 > 01/20/10 1518 3100 > 01/20/10 1515 2800 > 01/20/10 1440 200 > 01/20/10 1311 1100 > 01/20/10 1310 4000 > 01/20/10 1243 2700 > 01/20/10 1105 5100 > 01/20/10 1104 5800 > 01/20/10 938 4400 > 01/20/10 936 5100 > 01/20/10 936 300 > 01/20/10 935 4500 > 01/20/10 935 2500 > 01/20/10 807 1000 > 01/20/10 751 4200 > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Sorry about the platform thing. It's Informix 11 and I believe its on a Unix box. Your sample code works for the Date and Time, but not the seconds. SQL Error (-674): Routine (second) can not be resolved. Error Position: Ln: 20 Col: 8 James

Sorry, I thought that I'd fixed that. The seconds conversion should be doing the same kind of ((EXTEND(last_update,second to second)::CHAR(2))::INT) as the hours and minutes. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jan 21, 2010 at 2:18 PM, J. Hart <unleashedmaniac@gmail.com> wrote: > Sorry about the platform thing. It's Informix 11 and I believe its on > a Unix box. > > Your sample code works for the Date and Time, but not the seconds. > > SQL Error (-674): Routine (second) can not be resolved. > Error Position: Ln: 20 Col: 8 > > James > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Sweet... Thanks for the help. This was my final code... select last_update::DATE as activity_date, ((extend(last_update, hour to hour)::CHAR(2))::INT * 100) + ((extend(last_update, minute to minute)::CHAR(2))::INT) as activity_time, ((extend(last_update,second to second)::CHAR(2))::INT * 100) as activity_seconds from my_table