Sandokan Sandokan - 1 year ago 96
SQL Question

How to check if a string contains a date?

I'm trying to iterate on a

, this contain a results of query such as
, now the first three field contains a date, the format saved in the database table is this:

yyyy-MM-dd HH:mm:ss

but the code return this:

yyyy/MM/dd HH:mm:ss

in particular this:

For z = 1 To ds.Tables(0).Columns.Count - 1



So I need to recognize if the current string have this format:
yyyy/MM/dd HH:mm:ss
and parse it into:
yyyy-MM-dd HH:mm:ss
I tough to a
pattern for recognize it, but I'm not an expert of regex. Anyway, if there is another solution I'll glad to see. Note that only the first three value and the last one of the table is date, the other values aren't date but contain integer or other string value.

Answer Source

Dates do not have a format. From MSDN:

Represents an instant in time, typically expressed as a date and time of day.
Time values are measured in 100-nanosecond units called ticks, and a particular date is the number of ticks since 12:00 midnight, January 1, 0001 A.D. (C.E.) in the GregorianCalendar calendar...For example, a ticks value of 31241376000000000L represents the date, Friday, January 01, 0100 12:00:00 midnight.

So, a DateTime is just a Big Number. Representing them as "dd/MM/yyyy" is part of the magic of the DateTime type. Part of the issue is this:


Row items are Object. It wont act like a DateTime type unless/until you get it into a DateTime variable. That print as a DateTime simple because the DataTable knows the underlying type; but it will use the default format for your Culture. This makes it look like dates have a built in format (or even that the "format changed" if you tried to set it to something), but you are a human and 635882810022222112L would not make sense to most of us.

To change the output style, you first need to get it into a DateTime variable. Apparently, a preliminary step is to determine if an arbitrary column is a Date. Rather than testing the "format" of the output, test the underlying data type. This does assume a proper DateTime column in the DataTable:

If ds.Tables(0).Columns(n).DataType = GetType(DateTime) Then
End If
' Or:
If ds.Tables(0).Rows(x)(z).GetType Is GetType(DateTime) Then
End If

Then to change the display, first get it into a DateTime variable:

Dim dt As DateTime
If ds.Tables(0).Rows(x)(z).GetType Is GetType(DateTime) Then
    dt = Convert.ToDateTime(ds.Tables(0).Rows(x)(z))
    ' cant change "format" but you can change how it displays:
    Console.WriteLine(dt.ToString("yyyy-MM-dd HH:mm tt"))
    Console.WriteLine(dt.ToString("dd MMM, yyyy"))
End If

An easier way to get and convert to DateTime is to use the Field(Of T) extension:

Dim dt = ds.Tables(0).Rows(x).Field(Of DateTime)(y)

when I peform the insert usually do this: Date.Now.ToString("yyyy-MM-dd HH:mm:ss") so I apply a format to date to insert... if I don't format correctly the date as I shown I get this value 0000-00-00 00:00:00

That doesn't apply a format to a date. It converts the DateTime to a string. While "yyyy-MM-dd HH:mm:ss" is the correct format to use when passing date data as a string to MySql, it is not needed. The MySQL Data provider knows how to convert a Net DateTime var to the data MySql needs/wants and back again -- that's its job.

' this will work fine
cmd.Parameters.Add("@SomeDate", MySqlDbType.DateTime).Value = myDateTimeVar

The format requirement you read about is the what you need to use in the MySql shell or WorkBench UI because you are entering text/string there...from the keyboard. It does not mean code must convert DateTime variables to string in a specific format for storing.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download