Results 1 to 10 of 10

Thread: Convert String to DateTime for SQL Server Insert

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    14

    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.

  2. #2

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    14

    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?

  3. #3
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,580

    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.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    14

    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

  5. #5
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,580

    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.

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,442

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    14

    Re: Convert String to DateTime for SQL Server Insert

    That code printed this:

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

  8. #8
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,580

    Re: Convert String to DateTime for SQL Server Insert

    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  9. #9

    Thread Starter
    New Member
    Join Date
    Jul 2017
    Posts
    14

    Re: Convert String to DateTime for SQL Server Insert

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

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,442

    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.
    Quote Originally Posted by ftbadolato View Post
    (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
  •  



Click Here to Expand Forum to Full Width