Link to home
Create AccountLog in
Avatar of tim freese
tim freeseFlag for United States of America

asked on

SQLite Light

hi - thanks for looking.

would you please help me with the following sql statement using sqlite?

table d
ID int    
trxDate text
(no records in table - the text field is a string of date/time)

table t
ID int    
trxDate text

values for table t:
1, '11/15/2017 13:41'
2, '11/15/2017 13:45'

i need the following:

max of the date/time from table d.  if it is null, return the max date/time from table t.  if that is null, return the current date/time.

i tried to use the IFNULL function, but the second part of the function doesn't seem to be able to have a new SELECT statement within it.

any help would be appreciated.... thank you.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Please try this-

SELECT ID , CASE WHEN trxDate IS NULL THEN MAX(CAST(trxDate AS DATETIME)) OVER() ELSE CAST(trxDate AS DATETIME) END txtDate
from d

Open in new window

or this..

SELECT ID , ISNULL(CAST(trxDate AS DATETIME) , (SELECT MAX(trxDate AS DATETIME)) FROM d)) txtdate
from d

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of tim freese

ASKER

sqlite does not have a datetime data type - i have since modified the values of the date/time to be:

yyyyMMdd HH:mm:ss which i then changed your statement to:

SELECT COALESCE(
(SELECT MAX (trxDate) FROM [drops]),
(SELECT MAX (trxDate) FROM [recon]),
CURRENT_TIMESTAMP);

and it works great.

thank you.
Avatar of Bill Prew
Bill Prew

Great, glad that helped.


»bp