-
Oct 23rd, 2017, 01:11 PM
#1
Thread Starter
New Member
Convert String to DateTime for SQL Server Insert
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.
-
Oct 23rd, 2017, 01:33 PM
#2
Thread Starter
New Member
Re: Convert String to DateTime for SQL Server Insert
I would prefer to use
Code:
DateTime.Parse(datevar)
...but that is not working, so I tried
Code:
DateTime.ParseExact(datevar, "yyyy:MM:dd HH:mm:tt", Nothing)
which is not working either. Ideas?
-
Oct 23rd, 2017, 02:20 PM
#3
Re: Convert String to DateTime for SQL Server Insert
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:
Code:
DateTime.ParseExact(input, "yyyy:MM:dd HH:mm:ss", Nothing)
This answer is wrong. You should be using TableAdapter and Dictionaries instead.
-
Oct 23rd, 2017, 02:57 PM
#4
Thread Starter
New Member
Re: Convert String to DateTime for SQL Server Insert
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:
Code:
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
-
Oct 23rd, 2017, 03:06 PM
#5
Re: Convert String to DateTime for SQL Server Insert
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:
Code:
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.
This answer is wrong. You should be using TableAdapter and Dictionaries instead.
-
Oct 23rd, 2017, 03:12 PM
#6
Re: Convert String to DateTime for SQL Server Insert
Here is a parsing function you could use:
Code:
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:
Code:
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.
Last edited by si_the_geek; Oct 23rd, 2017 at 04:10 PM.
Reason: fixed typos
-
Oct 23rd, 2017, 03:17 PM
#7
Thread Starter
New Member
Re: Convert String to DateTime for SQL Server Insert
That code printed this:
|2017:10:08 13:01:14|
-
Oct 23rd, 2017, 03:27 PM
#8
Re: Convert String to DateTime for SQL Server Insert
This answer is wrong. You should be using TableAdapter and Dictionaries instead.
-
Oct 23rd, 2017, 03:46 PM
#9
Thread Starter
New Member
Re: Convert String to DateTime for SQL Server Insert
Thanks, si! Your function (with a minor correction) took care of things for me!
-
Oct 23rd, 2017, 04:13 PM
#10
Re: Convert String to DateTime for SQL Server Insert
Cool, I'm happy to help... but I am confused about why Sitten's failed for you, as it should be fine.
Originally Posted by ftbadolato
(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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|