SQL extract timestamp to get date & time

[itp]

Quick question.

I have Timestamp (Z) in a file and I want to extract date (8S) and time (4S)
using SQL. Is there a cool SQL way to do this or am I better off doing it in RPG?

[FaStOnE]

Re: SQL extract timestamp to get date & time

We have B.Houser ... of COURSE there's a COOL SQL way to do this ... you'll just have to wait on her reply .. I'm an idiot when it comes to that stuff.

[B.Hauser]

Re: SQL extract timestamp to get date & time

Hi,

no there is no cool way in SQL (at least I'm not aware of an easy way).

The following steps are necessary:
1. Date and time must be extracted from the timestamp
2. Date and time must be converted into a character representation of a date or time
3. The date and time separators must be removed from the string
4. The string without separators must be converted into a numeric value

The following solution will work on release V5R3 or higher:

Select Cast(Replace(Char(Date(MyTimeStamp), ISO), '-', '') as Dec(8, 0)) as MyDate,
       Cast(Replace(Char(Time(MyTimeStamp), ISO), '.', '') as Dec(6, 0)) as MyTime
   From MyTable 

Before release V5R3 the scalar functions SUBST and concat must be used to create the character representation of a date or time without separators.

Select Dec(Substr(Char(Date(MyTimeStamp), ISO), 1, 4) concat
           Substr(Char(Date(MyTimeStamp), ISO), 6, 2) concat
           Substr(Char(Date(MyTimeStamp), ISO), 9, 2), 8, 0) as NumDate,
       Dec(Substr(Char(Time(MyTimeStamp), ISO), 1, 2) concat
           Substr(Char(Time(MyTimeStamp), ISO), 4, 2) concat
           Substr(Char(Time(MyTimeStamp), ISO), 7, 2), 6, 0) as NumTime
From MyTable 

Birgitta

[itp]

Re: SQL extract timestamp to get date & time

Thanks Brigetta !

Actually formatted strings are just as good a a tad simpler.

Convert to date & time formats:
SELECT Date(TS) , Time(TS) FROM MYFILE

Convert to character representation of Date (YMD) & Time:
SELECT char(Date(TS)) , char(Time(TS)) FROM MYFILE