Avatar of freezingHot
freezingHot
Flag 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.
.NET ProgrammingSQL

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
Pawan Kumar

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

Pawan Kumar

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
Bill Prew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
freezingHot

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Bill Prew

Great, glad that helped.


»bp