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
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/
> create_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
