It would be nice if SQLite had a strptime-like function for things like this and not just strftime.
On Mon, Mar 29, 2010 at 10:13 AM, Griggs, Donald <donald.gri...@allscripts.com> wrote: > > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
