I currently have a CSV file uploaded into a sqlite 3.9.0 database with a column as Period that contains date in M/D/YYYY format as TEXT. I want to do some conditional operations on the database based on dates. I am unable to recognize them as dates. How do I recognize them as dates and do the operations?

  • Then how can I perform those operations? – Ankit Haldar Sep 3 '16 at 7:53

The date values must be in one of the supported date formats before you can do most operations on them.

You should configure whatever program that generates the CSV file to output the dates in a proper format. Alternatively, write a script to convert the values. If neither is possible, you could convert them with SQL statements, but SQLite does not have many string functions, so this would be not as easy.

| improve this answer | |

You could use the sqllite date and time functions to convert your string to date and then you can make your comparisons.

Probably using strftime('%d/%m/%Y',[your date])

| improve this answer | |
  • The internal date functions do not work if the format is not valid. – CL. Sep 3 '16 at 12:15
  • Created a new column based on the dates with the format YYYYMMDD as text. and could do all operations based on that. Thanks. – Ankit Haldar Sep 3 '16 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.