ah OK - being dull thank you 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
On 29 September 2016 at 14:29, Keith Medcalf <kmedc...@dessus.com> wrote: > You query is incorrect. It should be: > > SELECT CASE > 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; > > When your case, you are using the CASE <var> WHEN <value> ... > > So, the THEN clauses are comparing the value of DT to the result on (dt < > 10000000000) or (dt > 10000000000). The results of the expression are always > 1 or 0, which never equals DT, so the THEN clause is never executed and the > ELSE is always taken. > > >> -----Original Message----- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Paul Sanderson >> Sent: Thursday, 29 September, 2016 07:14 >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
