[Default] On Fri, 2 Nov 2007 17:29:23 +1100, T&B <[EMAIL PROTECTED]> 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 Often there are data cleaning and normalizing tasks to be performed before the data is ready for (read: can be piped into) the database. I would preprocess the data with an awk script. Perl or PHP or many other scripting tools are also suitable. Although it can be done, SQL just isn't the most elegant language to do these kind of things. -- ( Kees Nuyt ) c[_] ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
