CODEWITHSUNDEEP
sqlsqlitedatestatisticsdata-science
user9191983
user9191983

Reputation: 595

SQLite date function returns nothing

I have a dataset with column named "msg_dateStr" which contains user's accessing date and time. I tried to split it into two different columns; date and time, and I did SELECT date(msg_dateStr, 'localtime') as Year but it returns null straight. I don't know why this happens and how I can make sure something went wrong. Any advice will be appreciated.

Upvotes: 0

Views: 1990

Answers (1)

MikeT
MikeT

Reputation: 56953

The date to be used by the Date and Time Functions such as date MUST be in a format that is recognised by SQLite for a useful result. Recognised formats are (extract from the link above) :-

Time Strings

A time string can be in any of the following formats:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

You need to ensure that the data is saved accordingly or alternately (but not recommended at all) reformat the column via SQL (e.g. using the substr built-in function).

Upvotes: 1

Related Questions