You must have ISO8601 that has format YYYY-MM-DD not YYYY/MM/DD. If you compare strings it will not work. See result of this query:
select date('now', '-1 days')
you get: 2017-08-23
Regards
Radovan
Matthew Halliday je 24.08.2017 ob 15:21 napisal:
Looks like it's me. VBScript "NOW" = dd/mm/yyyy whereas ISO "NOW" = yyyy/mm/dd. Doh! Last part of the INSERT string reads: ,'" & strFreeSpace & "','" & pctFreeSpace & "','" & NOW & "');" Changing it to: ,'" & strFreeSpace & "','" & pctFreeSpace & "', datetime('NOW') ); so it inserts the SQLite 'NOW' instead. See how well that works. I need to update the other date/time stamps though. On Thu, Aug 24, 2017 at 2:06 PM, David Raymond <david.raym...@tomtom.com> wrote:The date data is in the format: "21/08/2017 16:27:03". I thought TEXT stored it in the ISO "YYYY/MM/DD" format?What you declare the field as doesn't matter, so much as what you actually store in it. But all the functions apart from strftime use YYYY-MM-DD HH:MM:SS so if you're giving them slashes and a different order then they're all gonna mess up.Correct: SELECT strftime(date_time, -2) as iDATE from tmp_dspace_import Result 21/08/2017 11:43:05 21/08/2017 11:43:05 21/08/2017 11:43:05That's saying use date_time as the format string where -2 is the time string. I'm surprised that actually gave an answer rather than an error. And that you're getting a result with slashes is really really suspicious.Then you can have query like this: select date_time, servername, drive, sum(diff_used) DailyUsed_mb from tmp_dspace_import where date_time >= julianday(current_date) - 1 group by date_time, servername, drive This would be for yesterday and today. If you need more days in past then change expression: date_time >= julianday(current_date) - 1 Just yesterday is then: date_time = julianday(current_date) - 1 for two days back: date_time >= julianday(current_date) - 2 etc...Don't forget that if you have a date and time in the database, then you're comparing a whole time to just a day there. I think you'd want something like where date(date_time) = date(current_date, "-1 days") --for yesterday where date(date_time) >= date(current_date, "-6 days") --for within the last week where date(date_time) between date(current_date, "-3 days") and date(current_date, "-1 days") --3 days ago to yesterday. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
