On 27 Mar 2010, at 10:46am, GeoffW wrote:
> Just for educational purposes I have been experimenting a little with > the Northwind Sqlite database contained on the sqlite official site. > Download link: http://download.vive.net/Northwind.zip. > > Am I misunderstanding here or are the dates in the wrong format for > sqlite within this converted database ? > Assuming it is wrong and not my understsanding, are there any easy > ways to get the dates reversed and corrected to sqlite order and > written back out to the database ? Perhaps this is better performed in the calling language, but the following sql should reformat these dates. Of course, you'd need to substitute and repeat for the other fields. -- Reformat date from, e.g., '1/5/2010 12:00:00 AM' to '2010-01-05' update employees set birthdate = replace (birthdate, ' 12:00:00 AM', ''); update employees set birthdate = '0' || birthdate where substr(birthdate, 2,1) == '/'; update employees set birthdate = substr(birthdate, 1, 3) || '0' || substr(birthdate, 4,99) where substr(birthdate, 5,1) == '/'; -- Date should now be formatted as dd/mm/yyyy -- Now change to yyyy-mm-dd update employees set birthdate = substr(birthdate, 7,4) || '-' || substr(birthdate, 1,2) || '-' || substr(birthdate, 4,2); _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
