CODEWITHSUNDEEP
sql-servervb.netdatetime
WLeo
WLeo

Reputation: 203

Error comparing datetime in SQL Server

I'm currently writing this program that the user will input the start date and end date. Then the program will retrieve the records from the database where the startdate in db will be >= user input start date and enddate in db will be <= user input end date.

Dim dateStr As DateTime = dtpStart.Value
Dim dateEnd As DateTime = dtpEnd.Value

MsgBox(dateStr)

con.ConnectionString = "Data Source=MKZSA065\SQLSERVER2008R2,1499;Initial Catalog=inv"
con.Open()
MsgBox("server connected")

cmd.Connection = con
'cmd.CommandText = "Select column_name as 'Columnname', data_type as'Datatype' from information_schema.columns where table_name = 'inv.dbo.InOutTransaction' AND column_name = 'inv.dbo.InOutTransaction.startDate'"
'cmd.CommandText = "Select inv.dbo.InOutTransaction.refNo, inv.dbo.Case_Status.deployedBy, CONVERT(varchar(10), inv.dbo.InOutTransaction.startDate, 120) as startDate, CONVERT(varchar(10), inv.dbo.InOutTransaction.endDate, 120) as endDate, inv.dbo.InOutTransaction.item FROM inv.dbo.InOutTransaction inner join inv.dbo.Case_Status ON inv.dbo.InOutTransaction.refNo = inv.dbo.Case_Status.refNo WHERE inv.dbo.InOutTransaction.refNo LIKE 'L%' AND inv.dbo.InOutTransaction.status LIKE 'Out'"

cmd.CommandText = "Select inv.dbo.InOutTransaction.refNo, inv.dbo.Case_Status.deployedBy, CONVERT(varchar(10),inv.dbo.InOutTransaction.startDate,120) as startDate, CONVERT(varchar(10),inv.dbo.InOutTransaction.endDate,120) as endDate, inv.dbo.InOutTransaction.item from inv.dbo.InOutTransaction inner join inv.dbo.Case_Status ON inv.dbo.InOutTransaction.refNo = inv.dbo.Case_Status.refNo WHERE inv.dbo.InOutTransaction.refNo LIKE 'L%' AND inv.dbo.InOutTransaction.status LIKE '%Out%' AND inv.dbo.InOutTransaction.startDate >= '" & dateStr & "' AND inv.dbo.InOutTransaction.endDate <= '" & dateEnd & "'"

Dim lrd As SqlDataReader
Dim li As ListViewItem

lrd = cmd.ExecuteReader()

Dim num As Integer = 1
While lrd.Read()
    ListView1.BeginUpdate()

    li = ListView1.Items.Add(num.ToString)
    li.SubItems.Add(lrd("refNo").ToString)
    li.SubItems.Add(lrd("deployedBy").ToString)
    li.SubItems.Add(lrd("startDate").ToString)
    li.SubItems.Add(lrd("endDate").ToString)
    li.SubItems.Add(lrd("item").ToString)
    'li.SubItems.Add(lrd("column_name").ToString)
    'li.SubItems.Add(lrd("data_type").ToString)

    num += 1

    ListView1.EndUpdate()
    ListView1.EnsureVisible(ListView1.Items.Count - 1)
End While

There was an error

Conversion failed when converting datetime from character string

How and what should I do :(

Upvotes: 0

Views: 68

Answers (2)

GarethD
GarethD

Reputation: 69819

USE PARAMETERISED QUERIES

Sorry to yell, but I cannot stress enough how important this is, not just to resolve your error, but for performance, and security of your application in general.

So instead of 

"inv.dbo.InOutTransaction.startDate >= '" & dateStr & "' AND inv.dbo.InOutTransaction.endDate <= '" & dateEnd & "'"

You need to use 

"inv.dbo.InOutTransaction.startDate >= @StartDate AND inv.dbo.InOutTransaction.endDate <= @EndDate"

Then add the parameters to your SqlCommand:

cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = dateStr
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = dateEnd

This means that there is no need for any implicit conversions, your SqlCommand is expecting two date parameters, and you pass two DateTime objects. All of a sudden the format of the dates, and cultural variations is irrelevant. Furthermore, it means that SQL Server can make use of cached plans and does not need to recompile the query for every different start and end date values passed.

As an aside, it is a good idea to use Using when dealing with IDisposable objects, especially with unmanaged objects like these. So you might end up with something like:

Dim dateStr As DateTime = dtpStart.Value
Dim dateEnd As DateTime = dtpEnd.Value

Dim sql as String = "Select inv.dbo.InOutTransaction.refNo, " & _
                    "       inv.dbo.Case_Status.deployedBy, " & _
                    "       CONVERT(varchar(10), " & _
                    "       inv.dbo.InOutTransaction.startDate,120) as startDate,  " & _
                    "       CONVERT(varchar(10),inv.dbo.InOutTransaction.endDate,120) as endDate,  " & _
                    "       inv.dbo.InOutTransaction.item  " & _
                    "from inv.dbo.InOutTransaction "
                    "   inner join inv.dbo.Case_Status  " & _
                    "       ON inv.dbo.InOutTransaction.refNo = inv.dbo.Case_Status.refNo  " & _
                    "WHERE inv.dbo.InOutTransaction.refNo LIKE 'L%'  " & _
                    "AND inv.dbo.InOutTransaction.status LIKE '%Out%'  " & _
                    "AND inv.dbo.InOutTransaction.startDate >= @StartDate  " & _
                    "AND inv.dbo.InOutTransaction.endDate <= @EndDate"


Using con As new SqlConnection("Data Source=MKZSA065\SQLSERVER2008R2,1499;Initial Catalog=inv;User ID=inv_admin;Password=inv_admin123")
Using cmd As New SqlCommand(sql, con)

    con.Open()
    cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = dateStr
    cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = dateEnd 

    Using lrd As SqlDataReader = cmd.ExecuteReader()

        Dim num As Integer = 1
        Dim li As ListViewItem

        While lrd.Read()
            ListView1.BeginUpdate()

            li = ListView1.Items.Add(num.ToString)
            li.SubItems.Add(lrd("refNo").ToString)
            li.SubItems.Add(lrd("deployedBy").ToString)
            li.SubItems.Add(lrd("startDate").ToString)
            li.SubItems.Add(lrd("endDate").ToString)
            li.SubItems.Add(lrd("item").ToString)
            'li.SubItems.Add(lrd("column_name").ToString)
            'li.SubItems.Add(lrd("data_type").ToString)

            num += 1

            ListView1.EndUpdate()
            ListView1.EnsureVisible(ListView1.Items.Count - 1)
        End While
    End Using
End Using

Please forgive my vb.Net if it is littered with errors, I haven't used it in a long time so am rusty to say the least, hopefully it is good enough to get the point across though

Upvotes: 3

DaveBally
DaveBally

Reputation: 113

I would imagine the the datestr is in a local format. Use the format YYYYMMDD as that is consistent regardless of DATEFORMAT settings.

Upvotes: 0

Related Questions