Need help with looping through date strings of yyyymmdd format

[IliaPreston]

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:

                string LastProcDateStr;             //     yyyymmdd

then, how do I write the loop that I explained in the above?

Please help.
Thanks.

[jmcilhinney]

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.

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);
    }
}

[IliaPreston]

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.

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:

            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:

    var mostRecentDate = DateTime.ParseExact(mostRecentDateText, "yyyyMMdd", null);
    var processingDate = mostRecentDate.AddDays(1);
    var currentDateTime = DateTime.Today;

To:

    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:

    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.

[jmcilhinney]

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.

[Zvoni]

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

create table Mytable (ID INT, MyDate TEXT);

INSERT INTO MyTable(ID,MyDate) VALUES(1,'2024-10-01'),(2,'2024-10-02'),(3,'2024-10-03');

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

[IliaPreston]

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

create table Mytable (ID INT, MyDate TEXT);

INSERT INTO MyTable(ID,MyDate) VALUES(1,'2024-10-01'),(2,'2024-10-02'),(3,'2024-10-03');

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.

[Zvoni]

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