When you have a base expression in the CASE, then it compares each of the WHEN values to that base value. So in your situation there you have it written like
if dt = (unix10and13.dt < 10000000000) then... if dt = (unix10and13.dt > 10000000000) then... I think if you get rid of the dt immediately after CASE then you should be good to go on that part. For the second part I think it would be something like select case when unix10and13.dt < 10000000000 then DateTime(unix10and13.dt, 'unixepoch') else null end as unix10, case when unix10and13.dt > 10000000000 then DateTime(unix10and13.dt / 1000, 'unixepoch') else null end as unix13 from unix10and13; -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson Sent: Thursday, September 29, 2016 9:14 AM To: General Discussion of SQLite Database Subject: [sqlite] converting unix10 and unix13 dates in the same column I have a table with dates in different formats, either 10 digit or 13 digit unix dates 1234345087123 1234567890 1432101234 1456754323012 I want a sql query that will convert both dates, I tried this SELECT CASE dt WHEN (unix10and13.dt < 10000000000) THEN DateTime(unix10and13.dt, 'unixepoch') WHEN (unix10and13.dt > 10000000000) THEN DateTime(unix10and13.dt / 1000, 'unixepoch') ELSE dt END AS converted FROM unix10and13 But this returns the original values - i.e. the else portion is being evaluated but one of the previous expressions should evaluate to true surely? Any ideas why this is failing? I am also interested (because I tried and failed) in coding a second query that would return all four rows but in two columns each with either a unix10 or 13 date in the correct column - something like this: unix10, unix13 ,1234345087123 1234567890, 1432101234, ,1456754323012 any suggestions to achieve this approach? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
