Search code examples
sqlitedatedatetimenullstrftime

OUTPUT <NULL> WHEN SELECTING DATE FROM DATETIME - SQLITE

I'm new to the SQL world and im going crazy trying to figure out how to SELECT date from a datetime field in SQLITE.

Example: value <11/11/2005 14:56>, i just want to select <11/11/2005> for EVERY ROW.

I tried strftime(), date(), CAST() and other functions but the output its always NULL. For example i tried querying SELECT strftime('%d/%m/%Y' , columnname) AS date FROM tablename; OUTPUT: "NULL" in every row

Can someone help me understand what im doing wrong and how can i fix it? Thank you!!!

Solution

  • It always returns NULL because MM/DD/YYYY is not a valid sqlite date format. Treat the column as a string and use substr and instr to drop off the time portion. Something like (no guarantees, check the doc!)

    SELECT substr(columname,0,instr(columnname,' '))

    Re comment "how to order by the date in descending order"

    This problem is a good argument (the best argument?) for storing the date in a sqlite date/time format. There is a strategy in this post for converting MM/DD/YYYY to YYYY-MM-DD (which sorts dates correctly).

    If it's not too late, it would be advisable to change the date storage to a valid sqlite date format. strftime can be used to present the date as desired, and sorting will be accurate.