R Smith.... WOW! Lol..... I just ran an overnight job to convert the dates to a more machine friendly format.
Looking at your code below, it is much more advanced than my skills can interpret. I will attempt to extract the code below (minus your conversion logic) to grab the days difference and generate my counts. I very much appreciate all your effort!!! And to the SQLite community as well! Regards, -Ron -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Wednesday, April 12, 2017 7:49 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question On 2017/04/12 1:24 AM, Ron Barnes wrote: > Hello all, > > To everyone who helped me before - thank you very much! > > I'm coding in Visual Basic .NET (Visual Studio 2015) Community. > > I have to count a Date/Time field and the problem is, this field contains > data in a format I'm not sure can be counted. > > I need to count all the dates in the field but the dates are a combined Date > and time in the format examples below. > My goal is to use the current Date/time ('NOW') and calculate the time > difference in days, from my DB Sourced field. > > I need to capture... > Less than 1 month old > 1 month old > 2 months old > 1 year old..... > all the way to greater than 10 years old. Your data is in a bad format as others pointed out, and probably the fastest solution would be to fix it in a program, however, SQLite can fix it. This next script will use CTE's to interpret the date, then reassemble it as ISO8601 date format and then calculate the elapsed days since that date. I've used your example dates in the test, plus added a few of mine to make sure we catch every possibility. To understand better what is happening, you can query any of the CTE tables (DA, DB, DC, etc.) in the main query. Also, I do the re-interpretation to use Julianday, but with a bit of cleverness, once you've interpreted the date constituents (CTE table DC below) you can already calculate the elapsed days, months or years. Have fun! -- Processing SQL in: E:\Documents\SQLiteAutoScript.sql -- SQLite version 3.17.0 [ Release: 2017-02-13 ] on SQLitespeed version 2.0.2.4. -- Script Items: 4 Parameter Count: 0 -- 2017-04-12 13:43:15.875 | [Info] Script Initialized, Started executing... -- ================================================================================================ CREATE TEMPORARY TABLE Volume_Information( ID INTEGER PRIMARY KEY, VI_Creation_Date TEXT ); INSERT INTO Volume_Information(VI_Creation_Date) VALUES ('10/30/2015 2:28:30 AM'), ('2/13/2016 7:51:04 AM'), ('5/15/2016 12:06:24 PM'), ('10/7/2016 1:27:13 PM'), ('3/3/2017 1:7:3 PM'), ('10/1/2016 6:59:18 AM'), ('10/09/2016 11:27:13 PM'); WITH DA(ID,DT) AS ( SELECT ID, replace(replace(replace(replace(VI_Creation_Date,' ',':'),'/',':'),'AM','0'),'PM','12')||':' FROM Volume_Information ), DB(i, k, l, c, r) AS ( SELECT DA.ID, 0, 1, DA.DT, -1 FROM DA UNION ALL SELECT i, k+1, instr(c, ':'), substr(c, instr( c, ':' ) + 1), CAST(trim(substr(c, 1, instr(c, ':') - 1)) AS INT) FROM DB WHERE l > 0 ), DC(ID, YY, MM, DD, HH, NN, SS, AP) AS ( SELECT DA.ID, MAX(CASE WHEN k=3 THEN DB.r ELSE 0 END), MAX(CASE WHEN k=1 THEN DB.r ELSE 0 END), MAX(CASE WHEN k=2 THEN DB.r ELSE 0 END), MAX(CASE WHEN k=4 THEN DB.r ELSE 0 END), MAX(CASE WHEN k=5 THEN DB.r ELSE 0 END), MAX(CASE WHEN k=6 THEN DB.r ELSE 0 END), MAX(CASE WHEN k=7 THEN DB.r ELSE 0 END) FROM DA, DB WHERE DA.ID = DB.i AND DB.r >= 0 AND DB.k > 0 GROUP BY DA.ID ), DD(ID, ISO_DT) AS ( SELECT ID, YY||'-'|| CASE WHEN MM > 9 THEN MM ELSE '0'||MM END||'-'|| CASE WHEN DD > 9 THEN DD ELSE '0'||DD END||' '|| CASE WHEN HH = 12 AND AP = 0 THEN '00' WHEN HH = 12 AND AP > 0 THEN AP WHEN HH + AP > 9 THEN HH + AP ELSE '0'||HH END||':'|| CASE WHEN NN > 9 THEN NN ELSE '0'||NN END||':'|| CASE WHEN SS > 9 THEN SS ELSE '0'||SS END FROM DC ) SELECT VI.ID, VI.VI_Creation_Date, DD.ISO_DT, printf('%0.1f',julianday('now')-julianday(DD.ISO_DT)) AS DaysSince FROM Volume_Information AS VI JOIN DD ON DD.ID = VI.ID ORDER BY VI.ID ; -- VI.ID | VI.VI_Creation_Date | DD.ISO_DT | DaysSince -- ------------ | ------------------------ | --------------------- | --------- -- 1 | 10/30/2015 2:28:30 AM | 2015-10-30 02:28:30 | 530.4 -- 2 | 2/13/2016 7:51:04 AM | 2016-02-13 07:51:04 | 424.2 -- 3 | 5/15/2016 12:06:24 PM | 2016-05-15 12:06:24 | 332.0 -- 4 | 10/7/2016 1:27:13 PM | 2016-10-07 13:27:13 | 186.9 -- 5 | 3/3/2017 1:7:3 PM | 2017-03-03 13:07:03 | 39.9 -- 6 | 10/1/2016 6:59:18 AM | 2016-10-01 06:59:18 | 193.2 -- 7 | 10/09/2016 11:27:13 PM | 2016-10-09 23:27:13 | 184.5 DROP TABLE Volume_Information; -- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.022s -- Total Script Query Time: -- --- --- --- --.---- -- Total Database Rows Changed: 7 -- Total Virtual-Machine Steps: 6304 -- Last executed Item Index: 4 -- Last Script Error: -- ------------------------------------------------------------------------------------------------ -- 2017-04-12 13:43:15.881 | [Success] Script Success. -- 2017-04-12 13:43:15.881 | [Success] Transaction Rolled back. -- ------- DB-Engine Logs (Contains logged information from all DB connections during run) ------ -- [2017-04-12 13:43:15.843] APPLICATION : Script E:\Documents\SQLiteAutoScript.sql started at 13:43:15.843 on 12 April. -- [2017-04-12 13:43:15.881] ERROR (284) : automatic index on DB(i) -- ================================================================================================ _______________________________________________ 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
