0

I have the following SQL statement, however it is including a date from October

The format from the csv line is 10/2/2017 17:32

Is it because my csv is incorrect?

Please help!

SELECT * 
FROM MyTable 
WHERE [Completion Status]= 'Incomplete' 
AND [Curriculum Name] NOT LIKE '%Phishing Training%' 
AND [Date Assigned] < date('now','-30 day') 
ORDER BY [Employee Department]
davidjbeiler
  • 133
  • 2
  • 15

2 Answers2

2

You should probably change the format of the date in your CSV. I don't think SQLite recognizes that format. Once you do that the answer from Olivier should work. https://www.sqlite.org/lang_datefunc.html

salvador
  • 66
  • 2
1

now (as of 17. Jan 2018) minus 30 days is 18. Dec 2017. Since you want [Date Assigned] < this date, i.e. before this date, a date from October is correct.

Did you intend to write 

[Date Assigned] >= date('now','-30 day')

i.e. return entries at most 30 days old?

Also, according to the official SQLite documentation, you should store the date as YYYY-MM-DD HH:MM. See: Date And Time Functions.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • i giet it, thanks but now its pulling dates from September :( – davidjbeiler Jan 17 '18 at 00:27
  • You should store the date as `YYYY-MM-DD HH:MM`. See: [Date And Time Functions](https://www.sqlite.org/lang_datefunc.html). – Olivier Jacot-Descombes Jan 17 '18 at 13:35
  • can i create a python function to alter this? – davidjbeiler Jan 17 '18 at 16:23
  • 1
    See [Converting string into datetime](https://stackoverflow.com/questions/466345/converting-string-into-datetime) for parsing and [How to print date in a regular format in Python?](https://stackoverflow.com/questions/311627/how-to-print-date-in-a-regular-format-in-python) for formatting. First parse the string from the CSV, then format it for SQLite. – Olivier Jacot-Descombes Jan 17 '18 at 16:35