Several years ago when I worked for a Fortune 70 company, we had a server whose source code and database were complicated by timestamps. I say complicated because there were different timestamp datatypes used for different fields (inherited from the data sources), the data could be stored in different formats, timestamp precision varied, and it was a problem to translate back and forth between the different representations and compare them. All of this added up to the occasional exercise of some obscure bugs in the server.
I successfully undertook a project to fix this for all time. My solution was very simple: all timestamps were represented as strings in the following format: "YYYYMMDD:HHmmSS.nnnnnn" This format, no what the original data source or format, became the standard format for timestamps on this particular server. Precision was to the microsecond for all data, even if represented by zeroes. This had several virtues: > When debugging software, all timestamps were readable when using Debug. Instead of looking at some binary number, the timestamp was easily human readable. > When using administrative tools to access the database, it was easy to examine, modify, and compare timestamps, since they were all human readable and in exactly the same format. > When comparing timestamps in the software to determine the most current, a simple string comparison always produced the correct result. The only feature that might have been needed (but wasn't on this particular server) was the ability to add or subtract time intervals from the timestamp. You may wish to consider a similar approach to managing your date and time information. Lee Crain ________________________________ -----Original Message----- From: T&B [mailto:[EMAIL PROTECTED] Sent: Friday, November 02, 2007 12:29 AM To: sqlite-users@sqlite.org Subject: [sqlite] Converting date from d/m/yy format 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] -------------------------------------------------------------------------- --- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
