Conversion failed when converting datetime from character string

Question

The string passed to my custom function is the following:

SELECT key FROM ubis WHERE MemberID = '144'
AND To >='11/7/2009 9:11:23 pm'
AND From <= '11/7/2009 9:11:23 pm'

    Public Shared Function GetDataTable(ByVal CmdText As String) As DataTable
        Dim myConn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
        Dim myCmd As New SqlCommand(CmdText, myConn)
        myConn.Open()
        Dim myReader As SqlDataReader = myCmd.ExecuteReader()
        Dim myTable As New DataTable()
        myTable.Load(myReader)
        myConn.Close()
        Return (myTable)
    End Function

and here is the error i get, Conversion failed when converting datetime from character string

I understand that the the datetime fields are passed as string into the function, but what options do i have?

Answer 1

11/7/2009 is ambiguous - is that 11th of July or 7th of November?

SQL has no way to tell - and it depends on the defaults it has been setup with. It would be better to pass in the date in an unambiguous format:

SELECT key FROM ubis WHERE MemberID = '144' 
               AND To >='11 July 2009 9:11:23 pm' 
               AND From <= '11 July 2009 9:11:23 pm'

Alternatively, use the correct conversion with the correct format code, or a custom one, as suggested by Zyphrax:

SELECT key FROM ubis WHERE MemberID = '144'
       AND To >= CONVERT(datetime, '11/7/2009 9:11:23 pm', 105)
       AND From <= CONVERT(datetime, '11/7/2009 9:11:23 pm', 105)

Answer 2

You can use the CONVERT command to convert the char to a datetime.

SELECT key FROM ubis WHERE MemberID = '144'
           AND To >= CONVERT(datetime, '11/7/2009 9:11:23 pm', 105)
           AND From <= CONVERT(datetime, '11/7/2009 9:11:23 pm', 105)

I'm not sure about the 105, you might have to Google for the right format code.

In addition if your SQL code encounters an exception, your connection won't be closed. You might want to add a bit of Using code to fix this.

Public Shared Function GetDataTable(ByVal CmdText As String) As DataTable        
 Using myConn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
   Using myCmd As New SqlCommand(CmdTxt, myConn)
     conn.Open()
       Using myReader As SqlDataReader = myCmd.ExecuteReader()
           Dim myTable As New DataTable()
           myTable.Load(myReader)
           myConn.Close()
           Return (myTable)  
      End Using
     End Using
End Function

Answer 3

Have you tried running the sql in managment studio and seeing what happens?

Answer 4

Have you considered using a parameterized query? This would solve your problem, and yield added security in the case where the WHERE conditions are taken from user input.

Example (VB.NET):

Dim myCmd As New SqlCommand(CmdText, myConn)
myCmd.Parameters.AddWithValue("MemberID", 144)
myCmd.Parameters.AddWithValue("Timestamp", DateTime.Now)

Used with this query text (SQL):

SELECT key FROM ubis WHERE MemberID = @MemberID
AND @Timestamp BETWEEN From AND To

Off-topic: the BETWEEN keyword in the SQL is just a neat way to express the >= AND <= conditions.

Answer 5

This smells of an sql injection vulnerability. That date didn't by any chance come from the user (even indirectly), did it? Even if this one is safe, generic functions like 'GetDataTable()' that don't account for good query parameters are almost always a mistake.

You want something more like this:

Public Shared Function GetMemberKeys(ByVal MemberID As Integer, ByVal KeyDate As DateTime) As DataTable

    Static sql As String= _
         "SELECT key" _
      + " FROM ubis" _ 
      + " WHERE MemberID= @MemberID AND @KeyDate BETWEEN [FROM] AND [TO]"

    Dim dt As New DataTable()
    Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString), _
          cmd As New SqlCommand(sql, cn)

        cmd.Parameters.Add("@MemberID", SqlDbType.Int).Value = MemberID
        cmd.Parameters.Add("@KeyDate", SqlDbType.DateTime).Value = KeyDate

        cn.Open()
        Using rdr As SqlDataReader = cmd.ExecuteReader()
             dt.Load(rdr)
        End Using
    End Using
    Return dt
End Function