Hi Jim,
I was able to get the SQL Working with this code From Ryan via the Mailing
List. Thank you for all your help!
SELECT cat, COUNT(*) AS qty
FROM (SELECT days, CASE
WHEN C.days < 1 THEN 'Under 1 Day'
WHEN C.days < 7 THEN 'Under 1 Week'
WHEN C.days < 31 THEN 'Under 1 Month'
WHEN C.days < 366 THEN 'Under 1 Year'
WHEN C.days < 731 THEN 'Under 2 Years'
WHEN C.days < 1826 THEN 'Under 5 Years'
WHEN C.days < 3651 THEN 'Under 10 Years'
ELSE 'Over 10 Years' END AS cat
FROM (SELECT julianday('now') -
julianday([REPLACE](substr(VI_Creation_Date, 1, 10), '/', '-')) AS days
FROM Volume_Information) C) G
GROUP BY cat
ORDER BY cat
Works great BTW
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Kam YiJie
Sent: Friday, April 14, 2017 6:37 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question
STOP SPAMM ING ING ME ASS FACE
________________________________
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of
Jim Callahan <jim.callahan.orla...@gmail.com>
Sent: Wednesday, April 12, 2017 11:21 AM
To: SQLite mailing list
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/images/syntax/expr.gif]<https://sqlite.org/lang_expr.html#glob>
SQLite Query Language: expression<https://sqlite.org/lang_expr.html#glob>
sqlite.org
If a numeric literal has a decimal point or an exponentiation clause or if its
magnitude is less than -9223372036854775808 or greater than
9223372036854775807, then ...
https://sqlite.org/lang_corefunc.html#glob
SQLite Query Language: Core
Functions<https://sqlite.org/lang_corefunc.html#glob>
sqlite.org
The core functions shown below are available by default. Date & Time functions,
aggregate functions, and JSON functions are documented separately.
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
