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:05
>
> That'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
