CODEWITHSUNDEEP
sqlsql-servervb.net
Vedran Turković
Vedran Turković

Reputation: 23

sql from vb cannot convert to date

Hi i'm trying to convert this line to get list of people active over todays date but cannot get it to work

adapter.SelectCommand = New SqlCommand( _
            "select * from klijent where convert(varchar,convert(datetime,replace('" & DateTimeUgovora.Value.ToString & "','#','')),111) >= convert(varchar,getdate(),111)", myConn)

error is conversion of varchar data type to a datetime data type resulted in an out-of-range value.

my string that I get from front is

"29.11.2013. 19:41:08"date problem

I searched everywhere and cannot find the answer please help

Upvotes: 0

Views: 688

Answers (2)

Ankit
Ankit

Reputation: 63

I just created a quick console application with the string mention by you. This may be helpful.

Imports System.Globalization

Module Module1

Sub Main()
    Dim myDateString As String = "29.11.2013. 19:41:08"
    myDateString = myDateString.Replace(".", "")
    Dim myDate As DateTime = DateTime.ParseExact(myDateString, "ddMMyyyy HH:mm:ss", CultureInfo.InvariantCulture)
    Console.WriteLine(myDate.ToString())
    Console.ReadLine()
End Sub

End Module

I created a quick module for tsql as well, maybe it will help:

Declare @dt varchar(20)
set @dt = '29.11.2013. 19:41:08'
select convert(datetime, Replace(@dt, '. ', ' '), 103)

Upvotes: 0

Markus
Markus

Reputation: 22511

You should not need to convert the datetime value to a string, because in SQL you can compare datetime values directly. This is much more stable as it doesn't depend on locale settings. I don't fully understand your SELECT clause as even if the comparison works, it will return either all the rows in the table or none.
However, if you want to use the table column Kli_Ugovor_do in your comparison, you can change your statement to this:

adapter.SelectCommand = New SqlCommand( _
            "select * from klijent where Kli_Ugovor_do >= getdate()", myConn)

Btw: in your statement you included the value of the combobox by string concatenation. You should get used to including parameters in your statements in order to avoid SQL injection attacks.
So if you want to use the value of the DateTimePicker, your code should look similar to this:

adapter.SelectCommand = New SqlCommand( _
            "select * from klijent where Kli_Ugovor_do >= @dt", myConn)
adapter.SelectCommand.Parameters.AddWithValue("@dt", dateTimeUgovora.Value)

Upvotes: 2

Related Questions