CODEWITHSUNDEEP
sqlvb.net
Gavin Crawley
Gavin Crawley

Reputation: 345

Check if date is less than another in SQL and VB.NET

I have 2 SQL tables (A & B) with dates in the format DD/MM/YYYY and time, for example 01/03/2012 00:00:00. In my VB.net application I am looking to count the number of records in table A which have a date less than the one in table B. I have no problem getting the dates from the database. I have extracted the date in table B and stored this in the program. Im just not sure how to compare the dates within the SQL query.

In short what I am trying to do is:

"select count(records) as totalRecords from table A where date < " & dateBvariable & ""

Any Help appreciated

Upvotes: 0

Views: 3771

Answers (3)

FosterZ
FosterZ

Reputation: 3911

try 

"select * from lk.media_months where begin_on < cast('" & dateVarialbe & "' as DATETIME)"

Upvotes: 1

Ian L
Ian L

Reputation: 5591

Try using a parameter to set the date. E.g.:

Dim sqlCmd As New SqlCommand("select count(records) as totalRecords from tableA where dateField < @DateParam")

sqlCmd.Parameters.AddWithValue("@DateParam", DateTime.Today)

Upvotes: 1

Andomar
Andomar

Reputation: 238078

There is a chance it'll work if you pass the date as a string in ' quotes:

where date < '" & dateBvariable & "'"

Or format it to the ODBC standard:

where date < '" & dateBvariable.ToString("yyyy-MM-dd HH:mm:ss") & "'"

Even better is to pass a parameter:

where date < @par1

And add the parameter to the SqlCommand:

command.AddWithValue "@par1", dateBvariable

Upvotes: 1

Related Questions