Convert String to DateTime for SQL Server Insert

[ftbadolato]

I have a string that looks like 2017:10:08 13:01:14 and simply need to convert it into an acceptable DateTime format for inserting into a SQL Server field of type datetime.

[ftbadolato]

I would prefer to use

DateTime.Parse(datevar)

...but that is not working, so I tried

DateTime.ParseExact(datevar, "yyyy:MM:dd HH:mm:tt", Nothing)

which is not working either. Ideas?

[Sitten Spynne]

Parsing DateTime is tricky. For example, the string you posted could just as easily represent October 8 or August 10, depending on region and preferences.

You have to get very familiar with this document if you have to do it.

It turns out you made a mistake at the end. The "tt" format specifier signifies the "AM/PM designator". So the format string you created would require noon on October 8 to be formatted: "2017:10:08 12:00:PM". But your input format has seconds in the place where you asked for AM/PM. You should have used the "ss" format specifier:

DateTime.ParseExact(input, "yyyy:MM:dd HH:mm:ss", Nothing)

[ftbadolato]

Thank you, good catch! However, I am still getting the same error:

String was not recognized as a valid DateTime.

Here is more code from the page, don't know if it will be helpful or not:

Dim img As Image = Image.FromFile(FileName)
Dim dateTaken As String
Dim dateforinsert As DateTime

Dim pitem As PropertyItem

If img.PropertyIdList.Contains(DATE_TAKEN) Then
            pitem = img.GetPropertyItem(DATE_TAKEN)
            dateTaken = Encoding.UTF8.GetString(pitem.Value, 0, pitem.Value.Length)
            dateforinsert = DateTime.ParseExact(dateTaken, "yyyy:MM:dd HH:mm:ss", Nothing)
 Else
            dateTaken = Nothing
 End If

[Sitten Spynne]

I can't see the input string. Be aware that ParseExact() is very picky, if there is so much as a leading or trailing space it will reject the string. I tested my code against your example string before posting it, so I know it works. That implies your string 'dateTaken' does not completely match the format. But since I can't see it, I can't tell you why.

One good way to catch mistakes, change your code to do this before the call to ParseExact:

Dim formattedDateTaken As String = String.Format("|{0}|", dateTaken)
MessageBox.Show(formattedDateTaken)
Debug.WriteLine(formattedDateTaken)

This will print the entire string with pipe characters around it. That can be helpful for seeing invisible content like spaces. The Debug.WriteLine() output should end up in your output window, you can copy that and paste it into your post.

[si_the_geek]

Here is a parsing function you could use:

Private Function ParseYMDHMS(theText as String) as DateTime
  Dim textParts as String() = theText.Split(" "c)
  Dim textDate as String() = textParts(0).Split(":"c)
  Dim textTime as String() = textParts(1).Split(":"c)
  Return New DateTime(CInt(textDate(0)), CInt(textDate(1)), CInt(textDate(2)),
                      CInt(textTime(0)), CInt(textTime(1)), CInt(textTime(2)))
End Function

usage:

dateforinsert = ParseYMDHMS(dateTaken)

Note however that this is untested, and it isn't designed to cope with invalid values (there are several points that things should be checked, eg: if textParts has more than 2 elements then something is wrong), but it might be good enough for now.

[ftbadolato]

That code printed this:

|2017:10:08 13:01:14|

[Sitten Spynne]

Works for me: https://dotnetfiddle.net/PVOGPi

[ftbadolato]

Thanks, si! Your function (with a minor correction) took care of things for me!

[si_the_geek]

Cool, I'm happy to help... but I am confused about why Sitten's failed for you, as it should be fine.

(with a minor correction)

Looking back I saw typo's, so I've fixed them... the note about validation etc still stands, but at least it's got you going for now.