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

Last Comment

Bill Prew

8/22/2022 - Mon

Pawan Kumar

11/15/2017

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

11/15/2017

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

11/15/2017

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

11/15/2017

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

11/15/2017

Great, glad that helped.

»bp