dcsimg
Results 1 to 8 of 8

Thread: [RESOLVED] Need help with Max(Date) & Min(Date) SQLite

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    547

    Resolved [RESOLVED] Need help with Max(Date) & Min(Date) SQLite

    Hello everyone
    Name:  27-04-2020 04-19-39.png
Views: 63
Size:  3.5 KB
    I want to query for the date in red.
    I tried:
    Code:
    StrSql = "Select Min(Date_Due) as latest FROM tbl" & _
     " where (due = 0)"
    The outcome is: 2019-02-22

    I tried with this query
    Code:
    StrSql = "Select Max(Date_Due) as latest FROM tbl" & _
     " where (due = 0)"
    The outcome is: 2021-12-05

    How shall I query for 2021-04-21?
    Thank you

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,152

    Re: Need help with Max(Date) & Min(Date) SQLite

    Hard to say based on what you provided as that is neither the min nor max date in your data shown
    Maybe if you used the min as above but added to the where clause to make sure that in addition to the due=0 that the date is not already past.

    Assuming of course that is what you are trying to attain

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,161

    Re: Need help with Max(Date) & Min(Date) SQLite

    ....should be in Database-Forum (reported)
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,527

    Re: Need help with Max(Date) & Min(Date) SQLite

    Moved
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,891

    Re: Need help with Max(Date) & Min(Date) SQLite

    Quote Originally Posted by newbie2 View Post
    Hello everyone
    Name:  27-04-2020 04-19-39.png
Views: 63
Size:  3.5 KB
    I want to query for the date in red.
    I tried:
    Code:
    StrSql = "Select Min(Date_Due) as latest FROM tbl" & _
     " where (due = 0)"
    The outcome is: 2019-02-22

    I tried with this query
    Code:
    StrSql = "Select Max(Date_Due) as latest FROM tbl" & _
     " where (due = 0)"
    The outcome is: 2021-12-05

    How shall I query for 2021-04-21?
    Thank you
    My guess is, that you want to retrieve the "most recent date of dates in the past".

    And the term I've underlined above, is currently not present in your SQLs Where-clause.

    So, when is a date in the past - and when is it in the future?
    Well, easy ... the past is < now ... and the future > now...

    So, in JET-SQL (*.mdb's) one would e.g. compare against built-in functions like:
    - Now() ... for a full DateTime-date
    - or Date() for only the DatePart (without the time)

    In SQLServer against:
    - GetDate()

    Whereas in SQLite, the datetime-functions ( https://www.sqlite.org/lang_datefunc.html ) are:
    - datetime() ... handing out a full date-time-string in ISO-format
    - date() ... handing out only the date-part of an ISO-date
    - time() ... handing out the time-part respectively

    But careful here, because in their short-form (without params) these functions hand out UTC-based date/time-values.
    So, if you stored your dates "local-time-based" in your DB, you will need a few modifier-params in those functions.

    The following are examples for the VB6-immediate-Window (just paste the line there, and press Return):

    Prints the current UTC-datetime:
    ?New_c.MemDB.GetRs("Select datetime()")(0)

    Prints the current local-datetime:
    ?New_c.MemDB.GetRs("Select datetime('now','localtime')")(0)

    The above "full-dates" only, to make you aware of the UTC'ness of SQLites default.

    For your case (since your Column contains Short-Dates, probably stored as local-dates), you will need :
    ?New_c.MemDB.GetRs("Select date('now','localtime')")(0)

    So your final SQL should introduce an additional And-condition for "dates in the past" like:
    Date_Due < date('now','localtime')

    Full SQL for the "most recent date of dates in the past":
    Select Max(Date_Due) as latest FROM tbl
    Where Due = 0
    And Date_Due
    < date('now','localtime')

    Or another SQL which might be useful in such scenarios ...
    (handing out all records "around now, plus-minus 3 days"):
    Select * FROM tbl Where Due = 0
    And Date_Due
    Between date('now','localtime','-3 days') And date('now','localtime','+3 days')

    HTH

    Olaf

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    547

    Re: Need help with Max(Date) & Min(Date) SQLite

    Thank you all of your interest
    Thank you Olaf of your help
    My guess is, that you want to retrieve the "most recent date of dates in the past".
    No but I want to mean the nearest future.
    I'm Sorry the attached image above is not very expressive.
    Name:  27-04-2020 04-19-39.png
Views: 30
Size:  3.8 KB
    this is part of my Vaccination project where I need to notify the parents of the next vaccin date.
    http://www.vbforums.com/showthread.p...cess-Vs-Sqlite

    thank you
    Last edited by newbie2; Apr 27th, 2020 at 02:55 PM.

  7. #7
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,891

    Re: Need help with Max(Date) & Min(Date) SQLite

    Quote Originally Posted by newbie2 View Post
    No but I want to mean the nearest future.
    Well, the full SQL for the nearest date of "all Dates-Due in the future" would be:
    Select Min(Date_Due) as NearestDateDue FROM tbl
    Where Due = 0
    And Date_Due
    > date('now','localtime')

    But I guess, you've deduced that already from the "inverse" example...

    Olaf

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    547

    Re: Need help with Max(Date) & Min(Date) SQLite

    Olaf
    You are very helpful
    million thanks sir
    solved

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width