Hello Ryan,
That Code below worked as you said it should. Awesome! And Thank you!
I now have the days difference for each row.
I have one other question if I may pose it to you, how do I count the number of
rows, less than a day, or a week or a year and so forth?
I tried this code and a few variants of it but I keep getting errors when
trying to execute.
Would you examine my code for errors?
SELECT category, COUNT(*) AS Expr1
FROM
((SELECT replace(substr(VI_Creation_Date, 1, 10), '/','-')||' 12:00:00'
AS ISO_Date, julianday('Now') - julianday(replace(substr(VI_Creation_Date,1,
10), '/','-')||' 12:00:00') AS DaysSince)
WHEN DaysSince < 2 THEN 'Under 1 Day'
WHEN DaysSince < 8 THEN 'Under 1 Week'
WHEN DaysSince < 32 THEN 'Under 1 Month'
WHEN DaysSince < 366 THEN 'Under 1 Year'
WHEN DaysSince < 366 THEN 'Under 1 Year'
WHEN DaysSince < 731 THEN 'Under 2 Year'
WHEN DaysSince < 1826 THEN 'Under 5 Years'
WHEN DaysSince < 3651 THEN 'Under 10 Years'
ELSE 'Over 10 Years' END) AS category
FROM Volume_Information) derivedtbl_1
GROUP BY category
Thanks,
-Ron
-----Original Message-----
From: sqlite-users [mailto:[email protected]] On
Behalf Of R Smith
Sent: Wednesday, April 12, 2017 8:32 AM
To: [email protected]
Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question
On 2017/04/12 2:13 PM, Ron Barnes wrote:
> Hi Jim,
>
> I ran an overnight job and converted 300+ million dates to the ISO 8601
> format.
>
> Here are examples of the new dates.
>
> 2017/04/10 07:24:15 PM
> 2017/03/07 08:08:58 AM
> 2016/11/06 12:35:15 PM
>
> Since this should be easier how would you go about determining the Day(s)
> Difference from the current date?
This is much friendlier. Do you care about the time? If not the conversion is
VERY easy:
SELECT replace(substr(VI_Creation_Date, 1, 10), '/','-')||' 12:00:00'
AS ISO_Date,
julianday('Now') - julianday(replace(substr(VI_Creation_Date,
1, 10), '/','-')||' 12:00:00') AS DaysSince
FROM Volume_Information
Cheers!
Ryan
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
