locked
Conversion failed when converting date and/or time from character string. RRS feed

  • Question

  • User-1423892740 posted

    i have no idea what to do..

    i use textbox with textbox mode = date

    and i use this code to save the data into DB 

     cmd.Parameters.Add("@CommenceDate", SqlDbType.Date).Value = Date.ParseExact(Me.tbCommenceDate.Text, "yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo, Globalization.DateTimeStyles.None)

    it works when i run it in VB 2013 for web but when i deploy it into server and run it "Conversion failed when converting date and/or time from character string." error came out..

    Monday, July 27, 2015 2:18 AM

Answers

  • User-84896714 posted

    Hi daen21,

    While line of code did the exception occured?  

    This line?

    cmd.Parameters.Add("@CommenceDate", SqlDbType.Date).Value = Date.ParseExact(Me.tbCommenceDate.Text, "yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo, Globalization.DateTimeStyles.None)

    Or this line I guess.

    cmd.ExecuteNonQuery();/cmd.ExecuteReader();/cmd.ExecuteScalar();

    If exception occured in first one please check user input is format like "yyyy-MM-dd". You could set a breakpoint and check what is the value of Me.tbCommenceDate.Text. If the exception occured in second line, kindly post more code. like your sql and other parameter.

    Best Regards,
    Wang Li

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 28, 2015 11:29 PM

All replies

  • User2024324573 posted

    Your server may have different date time setup.

    Monday, July 27, 2015 2:22 AM
  • User-1716253493 posted

    Make sure the the text and the format is correct

    for example

    if date text 27-07-2015 parseexact format should "dd-MM-yyyy"

    if date text 2015/07/27 parseexact format should "yyyy/MM/dd"

    and soon

    Monday, July 27, 2015 2:35 AM
  • User-1423892740 posted

    does it matter what my server date format.. cause it should convert the format into the one i put just when i wanna to save the date.. right?

    Monday, July 27, 2015 9:19 PM
  • User-1423892740 posted

    yeah the format i put is the same as format in my DB.. 

    Monday, July 27, 2015 9:20 PM
  • User-84896714 posted

    Hi daen21,

    Did the error occured during execute sqlcommand? If yes, you could change your code as follow.

    cmd.Parameters.Add("@CommenceDate", SqlDbType.Date).Value = Date.ParseExact(Me.tbCommenceDate.Text, "yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo, Globalization.DateTimeStyles.None).ToString("yyyy-MM-dd hh:mm:ss")

    Best Regards,
    Wang Li

    Monday, July 27, 2015 11:06 PM
  • User-1423892740 posted

    try already still return the same error..

    Tuesday, July 28, 2015 3:12 AM
  • User-84896714 posted

    Hi daen21,

    While line of code did the exception occured?  

    This line?

    cmd.Parameters.Add("@CommenceDate", SqlDbType.Date).Value = Date.ParseExact(Me.tbCommenceDate.Text, "yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo, Globalization.DateTimeStyles.None)

    Or this line I guess.

    cmd.ExecuteNonQuery();/cmd.ExecuteReader();/cmd.ExecuteScalar();

    If exception occured in first one please check user input is format like "yyyy-MM-dd". You could set a breakpoint and check what is the value of Me.tbCommenceDate.Text. If the exception occured in second line, kindly post more code. like your sql and other parameter.

    Best Regards,
    Wang Li

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 28, 2015 11:29 PM