Oops hit send too quickly the replace function replaces / with - in your date string to make the ISO 8601 and substr just makes sure we use the date portion only.
Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 12 April 2017 at 13:37, Paul Sanderson <sandersonforens...@gmail.com> wrote: > Hi Ron > > Your dates are still not 8601 > > with your dates above in a table called dateplay and column named vi > > select vi, > julianday('now') as now, > julianday(substr(replace(vi, '/', '-'), 1, 10)) as jday, > julianday('now') - julianday(substr(replace(vi, '/', '-'), 1, 10)) as > diff from > dateplay > > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 <+44%201326%20572786> > http://sandersonforensics.com/forum/content.php?195-SQLite- > Forensic-Toolkit -Forensic Toolkit for SQLite > email from a work address for a fully functional demo licence > > On 12 April 2017 at 13:13, Ron Barnes <rbar...@njdevils.net> 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? >> >> Thanks in advance, >> >> -Ron >> >> -----Original Message----- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Jim Callahan >> Sent: Tuesday, April 11, 2017 11:22 PM >> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement >> question >> >> This code: >> >> SELECT ( substr('02/13/2016',7,4) || '-' >> || substr('02/13/2016',1,2) || '-' >> || substr('02/13/2016',4,2) ) ; >> >> yields >> >> 2016-02-13 >> >> The above code, is dependent on fixed length strings (the leading zero) >> in other words '02/13/2016' and not '2/13/2016'. >> >> If you do not have fixed length date strings, you would probably have to >> use globs or regular expressions. >> >> *glob(X,Y)* >> >> The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note >> that the X and Y arguments are reversed in the glob() function relative to >> the infix GLOB <https://sqlite.org/lang_expr.html#glob> operator. >> >> https://sqlite.org/lang_corefunc.html#glob >> >> >> The REGEXP operator is a special syntax for the regexp() user function. No >> regexp() user function is defined by default and so use of the REGEXP >> operator will normally result in an error message. If an >> application-defined SQL function <https://sqlite.org/c3ref/crea >> te_function.html> named "regexp" >> is added at run-time, then the "*X* REGEXP *Y*" operator will be >> implemented as a call to "regexp(*Y*,*X*)". >> >> https://sqlite.org/lang_expr.html >> >> >> Type of regular expression needed: >> https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-15e2 >> -4e2c-8389-1266f496e4b2/regular-expression-to-get-date- >> format-from-string?forum=csharplanguage >> >> Jim Callahan >> >> On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes <rbar...@njdevils.net> >> wrote: >> >> > Hi Jim, >> > >> > I could alter the program that populates the Date/Time Column to the >> > format you specify. I'm trying real hard not to as that program has >> > been in use for many years and it would be a significant undertaking >> > to convert the program then convert the existing data. Not saying I >> > won't do it as I'm at that point, just wondering if it's possible to >> avoid that route. >> > >> > If I converted the date/time field, would it be easier to create counts? >> > >> > If you could, would you be able to offer a sample Select statement I >> > can alter to fit my needs? >> > >> > Thank you very much for the reply! >> > >> > Side note, I'll be visiting Disney in July! >> > >> > Regards, >> > >> > -Ron >> > >> > -----Original Message----- >> > From: sqlite-users >> > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> > On Behalf Of Jim Callahan >> > Sent: Tuesday, April 11, 2017 9:15 PM >> > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >> > Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement >> > question >> > >> > Can you convert the dates to ISO 8601 date time format? >> > https://en.wikipedia.org/wiki/ISO_8601 >> > >> > YYYY-MM-DD hh:mm:ss >> > >> > ISO date strings (when zero filled) are sortable which necessarily >> > includes comparable (Java speak). >> > By "zero filled" I mean for March you have "03" and not just "3". >> > >> > Then if you could generate/populate the boundary values in ISO format; >> > the comparisons would be straightforward and you could avoid the >> > julian date conversion. >> > >> > Another disadvantage of Julian dates are the different base years used >> > by applications including Unix, MS Access, MS Excel for Windows and MS >> > Excel for MacIntosh. Each application is internally consistent, but >> > the minute you exchange data between applications... >> > https://support.microsoft.com/en-us/help/214330/differences- >> > between-the-1900-and-the-1904-date-system-in-excel >> > >> > Your specification actually requires day counts; so you may need >> > Julian dates after all. >> > >> > Jim Callahan >> > Orlando, FL >> > >> > >> > >> > On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes <rbar...@njdevils.net> >> 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. >> > > >> > > Is this even possible in SQLite and if so, how would I go about doing >> it? >> > > >> > > I have been googling a few queries and come up blank. >> > > >> > > I try this code and differing combinations of it but it always >> > > returns NULL. >> > > >> > > SELECT CAST >> > > ((datetime(julianday(datetime('now'))) - >> > > JulianDay(VI_Creation_Date)) As >> > > Integer) >> > > FROM Volume_Information >> > > >> > > Here is what I have to work with. >> > > >> > > Table Name: >> > > Volume_Information >> > > >> > > Column name: >> > > VI_Creation_Date >> > > >> > > Date Format: >> > > MM/DD/CCYY HH:MM:SS AM/PM >> > > >> > > Examples: >> > > >> > > 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 >> > > >> > > Any Help would be greatly appreciated, >> > > >> > > Thanks, >> > > >> > > -Ron >> > > >> > > _______________________________________________ >> > > 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 >> >> _______________________________________________ >> 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
