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
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
