Results 1 to 7 of 7

Thread: Need help with looping through date strings of yyyymmdd format

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2010
    Posts
    806

    Need help with looping through date strings of yyyymmdd format

    I have an sqLite table that stores a number of records.
    Each record has a date column that represents the fact that anything related to that specific date has been processed.

    The application has a date box on the screen that allows the user to enter a specific date, and then click the "Process" button to process everything related to that date.
    The process checks to see if the record for that date exists in the table, doesn't do anything (except showing an error message to the user).
    But, if the record for that date does not exist, it processes everything for that date, and inserts a record for that specific date to the table.

    The above code has been already developed and works fine.
    Now, I need to add a new "Process All" button, and it should get the most recent date from that table, and in a loop, add one day to it and process for that date, and loop again, up until and including today's date.
    In other words, it should repeat the main process for all the dates that it has not processed before.

    The problem is, how to do that loop with ISO format dates that are actually of type string.
    A while back, in another thread on this forum, everybody advised me that the best way to store dates in a sqLite database table was to store them as "yyyymmdd" format as a string (in sqLite, the column is declared as TEXT, and when it is read into C# code, it is stored into a variable of type string).
    So, I have implemented it that way.

    Now, let's say I read the last process date from the database and store it in a variable like this:
    Code:
                    string LastProcDateStr;             //     yyyymmdd
    then, how do I write the loop that I explained in the above?

    Please help.
    Thanks.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,753

    Re: Need help with looping through date strings of yyyymmdd format

    One of the main reasons to use yyyyMMdd format for dates is that alphabetical order matches chronological order. That means that you can query that table, order descending by that column and get the first value to get the most recent date. I'll assume that you can write the data access code to get a string containing the yyyyMMdd representation of that most recent date from the database and provide code to perform the loop requested.
    Code:
    void DoSomething(string mostRecentDateText)
    {
        var mostRecentDate = DateTime.ParseExact(mostRecentDateText, "yyyyMMdd", null);
        var processingDate = mostRecentDate.AddDays(1);
        var currentDateTime = DateTime.Today;
    
        while (processingDate <= currentDateTime)
        {
            var processingDateText = processingDate.ToString("yyyyMMdd");
    
            // Do something here.
    
            processingDate = processingDate.AddDays(1);
        }
    }
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2010
    Posts
    806

    Re: Need help with looping through date strings of yyyymmdd format

    Quote Originally Posted by jmcilhinney View Post
    One of the main reasons to use yyyyMMdd format for dates is that alphabetical order matches chronological order. That means that you can query that table, order descending by that column and get the first value to get the most recent date. I'll assume that you can write the data access code to get a string containing the yyyyMMdd representation of that most recent date from the database and provide code to perform the loop requested.
    Code:
    void DoSomething(string mostRecentDateText)
    {
        var mostRecentDate = DateTime.ParseExact(mostRecentDateText, "yyyyMMdd", null);
        var processingDate = mostRecentDate.AddDays(1);
        var currentDateTime = DateTime.Today;
    
        while (processingDate <= currentDateTime)
        {
            var processingDateText = processingDate.ToString("yyyyMMdd");
    
            // Do something here.
    
            processingDate = processingDate.AddDays(1);
        }
    }
    Thanks a lot for your help.
    I copied the above code and added some test process instead of the Do Something, and it works perfectly.

    I then decided to check the data types of those variables that you have declared as "var" by doing this:
    Code:
                txtDetails.Text = Res + "\r\n\r\n" + mostRecentDate.GetType() + "\r\n\r\n" + processingDate.GetType() + "\r\n\r\n" + currentDateTime.GetType();
    So, the data types of all three of them is "System.DateTime"

    I changed the following lines from your code:
    Code:
        var mostRecentDate = DateTime.ParseExact(mostRecentDateText, "yyyyMMdd", null);
        var processingDate = mostRecentDate.AddDays(1);
        var currentDateTime = DateTime.Today;
    To:
    Code:
        System.DateTime mostRecentDate = DateTime.ParseExact(mostRecentDateText, "yyyyMMdd", null);
        System.DateTime processingDate = mostRecentDate.AddDays(1);
        System.DateTime currentDateTime = DateTime.Today;
    And again, it works perfectly.

    Then I changed them again (removed the System) to:
    Code:
        DateTime mostRecentDate = DateTime.ParseExact(mostRecentDateText, "yyyyMMdd", null);
        DateTime processingDate = mostRecentDate.AddDays(1);
        DateTime currentDateTime = DateTime.Today;
    And once again, it works perfectly.

    So, which one is better?
    Why do you use "var"?
    Why not use the explicit data type in this case "System.DateTime"?
    Why not use the explicit data type without the prefix in this case "DateTime"?

    Please advise.
    Thanks again.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,753

    Re: Need help with looping through date strings of yyyymmdd format

    As far as using DateTime rather than System.DateTime, that's the whole purpose of importing namespaces at the top of your code file or, as is supported in recent versions, in a global file. Code gets very cluttered if you fully-qualify every data type, so everyone tends to import namespaces and use just the type names. You can still fully-qualify a type name if it's ambiguous or you want to make it obvious where an obscure type comes from.

    As for 'var', it was originally introduced to support LINQ and anonymous types. When a LINQ query returns an anonymous type, you obviously can't declare the variable it's assigned to as a specific type, so you must use 'var' and the type is inferred from the initialising expression. because that makes code more succinct, most people tend to use 'var' pretty much all the time when initialising a variable where it's declared, allowing the data type of the variable to be inferred from the initialising expression. When viewing code in VS, you can simply mouse over the variable to see it's type, although it can be harder to determine the type in situations where Intellisense is not available, e.g. code posted in sites like this one. Most experienced developers tend to use 'var' whenever they can because it's easier, only using an explicit type where it's necessary, e.g. where the variable type should be an interface but the initialising expression returns a concrete class, or where the type is not clear from the initialising expression.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,784

    Re: Need help with looping through date strings of yyyymmdd format

    On a sidenote:
    "YYYYMMDD" is WRONG to store in SQLite
    https://sqlite.org/lang_datefunc.html

    Correct: YYYY-MM-DD

    If you store the date correctly, you can even use Date-Functions direclty in SQL to return the timeframe you are intersted in
    https://dbfiddle.uk/Nz0FvxLu
    Code:
    create table Mytable (ID INT, MyDate TEXT);
    Code:
    INSERT INTO MyTable(ID,MyDate) VALUES(1,'2024-10-01'),(2,'2024-10-02'),(3,'2024-10-03');
    Code:
    WITH
    DT AS (SELECT ID, MyDate, ROW_NUMBER() OVER(ORDER BY Mydate DESC) AS RN FROM MyTable),
    CT AS (SELECT Date(MyDate,'localtime','+1 days') As MyDate FROM DT WHERE RN=1
             UNION ALL
             SELECT Date(MyDate,'localtime','+1 days') As MyDate FROM CT WHERE MyDate<Date('now','localtime'))
    SELECT * FROM CT
    Returns

    MyDate
    2024-10-04 --> max-Date +1
    2024-10-05
    2024-10-06
    2024-10-07
    2024-10-08
    2024-10-09
    2024-10-10 --> Today
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE 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.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2010
    Posts
    806

    Re: Need help with looping through date strings of yyyymmdd format

    Quote Originally Posted by Zvoni View Post
    On a sidenote:
    "YYYYMMDD" is WRONG to store in SQLite
    https://sqlite.org/lang_datefunc.html

    Correct: YYYY-MM-DD

    If you store the date correctly, you can even use Date-Functions direclty in SQL to return the timeframe you are intersted in
    https://dbfiddle.uk/Nz0FvxLu
    Code:
    create table Mytable (ID INT, MyDate TEXT);
    Code:
    INSERT INTO MyTable(ID,MyDate) VALUES(1,'2024-10-01'),(2,'2024-10-02'),(3,'2024-10-03');
    Code:
    WITH
    DT AS (SELECT ID, MyDate, ROW_NUMBER() OVER(ORDER BY Mydate DESC) AS RN FROM MyTable),
    CT AS (SELECT Date(MyDate,'localtime','+1 days') As MyDate FROM DT WHERE RN=1
             UNION ALL
             SELECT Date(MyDate,'localtime','+1 days') As MyDate FROM CT WHERE MyDate<Date('now','localtime'))
    SELECT * FROM CT
    Returns

    MyDate
    2024-10-04 --> max-Date +1
    2024-10-05
    2024-10-06
    2024-10-07
    2024-10-08
    2024-10-09
    2024-10-10 --> Today
    Thanks a lot for your comments.

    But, I am quite confused.
    Everybody on this forum advised me a while back (in another thread) that the right way to use dates with sqLite was to declare them as Text and store the values as "YYYYMMDD" that is without dashes.
    But, now you are saying that that is wrong.
    I am not sure which one to believe.

    Aside from that, another problem with dashes is that the resulting string can be ambiguous.
    For example, is "2024-03-08" March 8th, 2024 or is it August 3th, 2024?
    But, the YYYYMMDD format (without dashes) does not pose that kind of ambiguity. The date "20240308" can be interpreted and understood in only one way.

    Can other people please also comment on this issue?
    What is the consensus? YYYYMMDD or YYYY-MM-DD?

    Thanks.

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,784

    Re: Need help with looping through date strings of yyyymmdd format

    Quote Originally Posted by IliaPreston View Post
    Thanks a lot for your comments.

    But, I am quite confused.
    Everybody on this forum advised me a while back (in another thread) that the right way to use dates with sqLite was to declare them as Text and store the values as "YYYYMMDD" that is without dashes.
    But, now you are saying that that is wrong.
    I am not sure which one to believe.

    Aside from that, another problem with dashes is that the resulting string can be ambiguous.
    For example, is "2024-03-08" March 8th, 2024 or is it August 3th, 2024?
    But, the YYYYMMDD format (without dashes) does not pose that kind of ambiguity. The date "20240308" can be interpreted and understood in only one way.

    Can other people please also comment on this issue?
    What is the consensus? YYYYMMDD or YYYY-MM-DD?

    Thanks.
    There is no ambiguity --> https://www.sqlite.org/lang_datefunc.html
    SQLite does not have a dedicated date/time datatype. Instead, date and time values can stored as any of the following:
    ISO-8601 A text string that is one of the ISO 8601 date/time values shown in items 1 through 10 below. Example: '2025-05-29 14:16:00'
    Julian day number The number of days including fractional days since -4713-11-24 12:00:00 Example: 2460825.09444444
    Unix timestamp The number of seconds including fractional seconds since 1970-01-01 00:00:00 Example: 1748528160
    "2024-03-08" will always be March 8th 2024 according to ISO8601
    If the Time-Part is missing it's assumed to be "00:00:00"

    EDIT: I have to amend my comment:
    YYYYMMDD is still "correct" regarding ISO8601 --> BUT NOT FOR SQLITE to use the InBuilt Date/Time-Function
    https://en.wikipedia.org/wiki/ISO_8601

    In "General Principles"
    Representations can be done in one of two formats – a basic format with a minimal number of separators or an extended format with separators added to enhance human readability.[15][16] The standard notes that "The basic format should be avoided in plain text."[17] The separator used between date values (year, month, week, and day) is the hyphen, while the colon is used as the separator between time values (hours, minutes, and seconds). For example, the 6th day of the 1st month of the year 2009 may be written as "2009-01-06" in the extended format or as "20090106" in the basic format without ambiguity.
    Everything said: I prefer "YYYY-MM-DD" for readability
    Last edited by Zvoni; Oct 31st, 2024 at 06:36 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE 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.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width