Conversion of string to date type while passing from vb.net to sql server

Question

I had the same issue while passing date variable from vb.net to SQL Server. I tried with Parameters as suggested above. Still I am facing the same issue. Pl guide.

Dim today As String
today = System.DateTime.Now.ToString("MM-dd-yyyy hh:mm:ss")
Dim todate As DateTime = DateTime.ParseExact(today, "MM-dd-yyyy hh:mm:ss", System.Globalization.DateTimeFormatInfo.InvariantInfo)
SQLCom.CommandType = CommandType.Text
SQLCom.Parameters.AddWithValue("@TODAY", CType(todate, Date))
SQLCom.CommandText = "SELECT DATEDIFF(MM,FORMAT(UPD_DATE,'dd-MM-yyyy'),FORMAT(@TODAY,'dd-MM-yyyy')) AS DATE_DIFF FROM tblEMP WHERE EMPID = '" & ID & "';"
Try
        Dim daEmpExp As New SqlDataAdapter
        daEmpExp.SelectCommand = SQLCom
        SQLConn.Open()
        daEmpExp.Fill(dsEmpExp)
        SQLConn.Close()
    Catch ex As Exception
        MsgBox("Sorry!!! " & ex.Message)
    Finally
        If SQLConn.State = ConnectionState.Open Then
            SQLConn.Close()
        End If
    End Try
End If
Return dsEmpExp

Still it catches error "Conversion failed when converting date and/or time from character string."

You should edit your question. It is not properly formatted. — Dhwani
– Dhwani, Commented Jun 21, 2014 at 7:26
I think your problem relates from switching from MM-dd to dd-MM — Paul Michaels
– Paul Michaels, Commented Jun 21, 2014 at 7:39

har07 · Accepted Answer · 2014-06-21 08:48:20Z

1

Why you need to convert date to string back and forth? Try to simply pass DateTime.Now as sql command parameter :

SQLCom.CommandType = CommandType.Text
SQLCom.Parameters.AddWithValue("@TODAY", System.DateTime.Now)
SQLCom.Parameters.AddWithValue("@ID", ID)
SQLCom.CommandText = "SELECT DATEDIFF(MM,FORMAT(UPD_DATE,'dd-MM-yyyy'),FORMAT(@TODAY,'dd-MM-yyyy')) AS DATE_DIFF FROM tblEMP WHERE EMPID = @ID;"
Try
    ......
End Try

answered Jun 21, 2014 at 8:48

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conversion of string to date type while passing from vb.net to sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related