从字符串转换datetime时转换失败

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

这是我得到的错误,从字符串转换datetime时转换失败

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

我知道datetime字段作为字符串传递给函数,但我有哪些选项?

5 个解决方案

#1

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

您是否尝试在管理工作室中运行sql并查看会发生什么?

#2

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.

您是否考虑过使用参数化查询?这将解决您的问题,并在从用户输入获取WHERE条件的情况下提高安全性。

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):

与此查询文本(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.

偏离主题:SQL中的BETWEEN关键字只是表达> = AND <=条件的简洁方式。

#3

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.

这有一个sql注入漏洞的气味。那个日期没有任何机会来自用户(甚至间接),做到了吗?即使这个是安全的,像'GetDataTable()'这样没有考虑好查询参数的泛型函数几乎总是一个错误。

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

#4

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

11/7/2009是模棱两可的 - 是7月11日还是11月7日?

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:

SQL无法分辨 - 它取决于它已经设置的默认值。最好以明确的格式传递日期:

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:

或者,根据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)

#5

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

您可以使用CONVERT命令将char转换为日期时间。

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.

我不确定105,您可能需要Google才能获得正确的格式代码。

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.

此外,如果您的SQL代码遇到异常,则不会关闭您的连接。您可能需要添加一些使用代码来解决此问题。

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

#1