I'm trying to iterate on a
SELECT * FROM tb 1
For z = 1 To ds.Tables(0).Columns.Count - 1
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.
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
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
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.ToLongDateString) 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.