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:
Who is Participating?
 
Bill PrewCommented:
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
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
or this..

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

Open in new window

0
 
freezingHotAuthor Commented:
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.
0
 
Bill PrewCommented:
Great, glad that helped.


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.