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.

LVL 1

freezingHotAsked: 2017-11-15

Who is Participating?

LVL 62

Bill PrewCommented: 2017-11-15

This should do what you described. Given the format of your text date/time strings in the database I had to do some conversion gymnastics to make it a datetime type for the MAX() selection.

SELECT COALESCE(
(SELECT MAX (DATETIME (SUBSTR ([trxDate], 7, 4) || '-' || SUBSTR ([trxDate], 1, 2) || '-' || SUBSTR ([trxDate], 4, 2) || SUBSTR ([trxDate], 11))) FROM [d]),
(SELECT MAX (DATETIME (SUBSTR ([trxDate], 7, 4) || '-' || SUBSTR ([trxDate], 1, 2) || '-' || SUBSTR ([trxDate], 4, 2) || SUBSTR ([trxDate], 11))) FROM [t]),
CURRENT_TIMESTAMP);

Open in new window

»bp

LVL 38

Pawan KumarDatabase ExpertCommented: 2017-11-15

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

LVL 38

Pawan KumarDatabase ExpertCommented: 2017-11-15

or this..

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

Open in new window

LVL 1

freezingHotAuthor Commented: 2017-11-15

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.