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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Wednesday, April 12, 2017 8:32 AM To: sqlite-users@mailinglists.sqlite.org 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 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