How to check if system date format is different then stored in mysql

Question

Sometimes users play with system date format. How can I check if system date is changed and different then my stored date format.

I found a solution but it seems to me not very good. I compare the year of the system_date and data_date via sysdate.Substring(0,4) and DataDate.Substring(0,4)

But the user can change the date yyyy-dd-MM or to another format.

Is there a more accurate way to do ?

    public static bool DateFormatChanged()
    {
        bool result=true;
        string Sys_date = DateTime.Now.ToString("d");
        string Data_date="";
        using (MySqlConnection conn = new MySqlConnection(PublicVariables.cs))
        {
            conn.Open();
            using (MySqlCommand cmd = new MySqlCommand("SELECT MAX(st_date) as d_date FROM stockdata", conn))
            {
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    Data_date = reader["d_date"].ToString();
                    if(Sysdate.Substring(0,4) != Data_date.Substring(0,4))
                        result =false;
                }
            }
        }
        return result;
    }

I think the best way is setting the system format at the begining but I don't know how to do.

Data before restoration

Data restored

Database after restoration

Someone has changed the date time to another format I did not pay attention during the backup and the result is above. Accident was waiting this day! When I used the backup of day before and put the format to it's value everything was good except one day lost.

if you use `SubString` to check a date, it is not a Date but a string. Dates dont have a format, they *can* be displayed in various formats — Ňɏssa Pøngjǣrdenlarp, Dec 16 '14 at 19:46
...and if `st_date` is defined as string/char in the database, `MAX` will be performing a text comparison and give the wrong result with a wide variety of values — Ňɏssa Pøngjǣrdenlarp, Dec 16 '14 at 19:58
st_date is in Date format. I did not find a accurate solution for cheking month. Sure the base is what on on data. The date of system must be coherent with date in data. I store by default yyyy-MM-dd format. — Ismail Gunes, Dec 16 '14 at 21:11
Dates do not have a format (they are simply a value), so if you are setting a specific format such as "yyyy-MM-dd" it cant be a date column. — Ňɏssa Pøngjǣrdenlarp, Dec 16 '14 at 21:22

Andrew Morton · Answer 1 · 2014-12-16T20:04:00.807

1

Your database has the dates stored as dates, not strings, doesn't it?

To examine the current UI date format, you can use

Dim cc = Threading.Thread.CurrentThread.CurrentUICulture
Dim dateFormat = cc.DateTimeFormat.ShortDatePattern
Console.WriteLine(dateFormat) ' outputs, e.g., dd/MM/yyyy

It should not matter to your program how dates are displayed: it is up to the user how they want to see them, and your program should respect that choice.

edited Dec 16 '14 at 20:04

answered Dec 16 '14 at 19:51

Andrew Morton

24,203
9
60
84

I changed your advise in c# var cc = Thread.CurrentThread.CurrentUICulture; var dateFormat = cc.DateTimeFormat.ShortDatePattern; But it gives me M/d/yyyy how ever my date format is userdefined and yyyy-MM-dd ?? What can be the bug ? – Ismail Gunes Dec 16 '14 at 21:57
There are several possibilities, perhaps you need to check `DateTimeFormat.LongDatePattern`. Oh, and as you didn't comment on the first line of my answer, the bug would appear to be that you are storing dates as strings instead of dates. – Andrew Morton Dec 16 '14 at 22:01
I did not check the format of the database date. I don't know how to do. What I check is only the format of the system date and compare with date in data. Why I need this facility ? Because in every part of 12 forms there is string SubFolder = DateTime.Now.ToString("d"); I have to change all those lines where ever they are with string SubFolder = DateTime.Now.ToString("yyyy-MM-dd"); It's very annoying. – Ismail Gunes Dec 16 '14 at 22:43
@IsmailGunes 1) It looks like you have found the correct way to do it. 2) A date does not have a format, only the *representation* of the date does. – Andrew Morton Dec 17 '14 at 09:54
Yes, that's true. When I change the format in client computer and do an entry of date addwithvalue with a different format then server, during read it gives an error. When I look with phpmyadmin I see that the date is 0000-00-00 or something like this. So that forces me to enter all the dates with Convert.ToDateTime(...). Either I will add everywhere this convertion function or check date format at the begining of the program – Ismail Gunes Dec 29 '14 at 16:21

score 0 · Answer 2 · edited May 23 '17 at 12:05

There is a post about this. It basically explains how to compare the format of 2 date strings which in your case is simply dates.

The discussion is located at : Parsing the DateTime format to get Format String

As John Skeet indicated "It would be very hard to do this in a completely general way".

The following is a long way but I think it will work comparing the 2 different dates (as long as the dates are the same. For different dates, you can replace the numbers with a specific character for equalization and then check) with different formats;

public static bool CompareDates(DateTime Date1, DateTime Date2)
{
    int Datetime1Format = 0;
    int Datetime2Format = 0;
    var Date1Formats = Date1.GetDateTimeFormats();
    var Date2Formats = Date2.GetDateTimeFormats();
    for (int i = 0; i < Date1Formats.Length; i++)
    {
        if (Date1Formats[i] == Date1.ToString())
            Datetime1Format = i;
    }
    for (int k = 0; k < Date2Formats.Length; k++)
    {
        if (Date2Formats[k] == Date2.ToString())
            Datetime2Format = k;
    }

    if (Datetime1Format == Datetime2Format)
        return true;
    else
        return false;

}

Based on your comment, you probably could use something like the following;

            var Comp1 = Regex.Replace(Date1.ToString(), @"[\d-]", string.Empty);
            var Comp2 = Regex.Replace(Date2.ToString(), @"[\d-]", string.Empty);

            if (Comp1 == Comp2)
                return true;
            else
                return false;

What this basically does is, gets the dates, replaces the numbers with empty string since the dates are different then compares the rest. This may not work with some of the formatting e.g(14/12/2014 vs 12/14/2014) but if the actual layout of the date is different then it should give you an idea.

As you see in my method I take the last(MAX) date from data and look if system data is like in data, otherwise someone has changed it. I don't want check all date format. When the program lunches first time writes data suitable to the cultureinfo. — Ismail Gunes, Dec 16 '14 at 21:23
First of all, I am not sure why this is necessary, maybe for formatting but you can always provide the formatting to the user in a default concept. If you want to check the culture info and the dates are different(which I would assume since you select the system date) then as I indicated in my answer, replace every number with a character then check if they equal to each other. e.g (XXXX-XX-XX XX:XX vs XXXX/XX/XX XX:XX) — Hozikimaru, Dec 16 '14 at 21:34
I have a method which gets a backup at the end of year only the products and materials in stock, then I save this data in cvs format in my computer (or I take the backup within my program). If date format is changed between backup or restore all the dates are blown up after restore. This is why I need this check. — Ismail Gunes, Dec 29 '14 at 16:32

How to check if system date format is different then stored in mysql

2 Answers2