T&B wrote:
Hi all,
How can I convert dates from the format d/m/yy to SQL style YYY-MM-DD?
I have some imported data that includes a date column in the format d/
m/yy, where:
d = day as 1 or two digits
m = month as 1 or two digits
yy = year as two digits
eg:
2/11/07 = today
2/8/68 = 2nd of August, 1968
How can I convert this in SQLite to YYYY-MM-DD?
The data is from a bank, so I have no control over its production.
I couldn't find any suitable built in SQLite functions, which all seem
to operate in the other direction.
The best I've come up with so far is:
create table Raw( Date );
insert into Raw( Date ) values ( '2/11/07' );
insert into Raw( Date ) values ( '2/8/68' );
select
case /* Prefix for year = 20 if year starts 0, 1 or 2, else 19 */
when cast( substr( Date, -2, 1 ) as integer ) < 3
then '20'
else '19'
end
|| substr( Date, -2, 2 ) /* Year = last two characters */
|| '-' ||
case /* Prefix month with 0 if short */
when substr( Date, -5, 1 ) = '/'
then '0'
else ''
end
||
case /* Month = from after / to 4th last character */
when substr( Date, 2, 1) = '/'
then substr( Date, 3, length( Date ) - 5 )
else substr( Date, 4, length( Date ) - 6 )
end
|| '-' ||
case /* Day = from 1st to character before first / */
when substr( Date, 2, 1 ) = '/'
then '0' || substr( Date, 1, 1 ) /* Prefix with 0 if short */
else substr( Date, 1, 2 )
end
as Date
from Raw
;
which correctly gives:
2007-11-02
1968-08-02
But is there a more robust, built in method?
Thanks,
Tom
If you transform the date into the internal Sqlite format on data
aquisition. Then you can use the internal Sqlite date functions to
present the date in the format of your choice.
Physically Sqlite stores a date as a floating point Julian date based on
an astronomical epoch.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
