Filter on the date part of a SQL datetime field

Hey,

I have a VB.NET application which works with a SQL database. In the SQL I have a table which contains 2 datetime values.

In VB I want my application to show all records with a datetime value in a certain filter. The probem is that the filter in VB is a date filter.

My question is how can I check on the date part of a datetime column in SQL. The time can be whatever it is, as long as the date is correct.

My problem:
in Vb the filter is 01/01/01 00:00:00
In SQL the FromDateTimefield is 01/01/01 13:00:00 and the ToDateTimeField is 01/01/01 14:00:00

sqlCmd = New SqlCommand("SELECT * FROM Table WHERE @d BETWEEN FromDateTime AND ToDateTime", objSQLConn)
sqlCmd.Parameters.Add("@d", datDate)
=> @d is not between the fromdatetime and the todatetime because of the 00:00:00 part.

Tnx for the help!
LVL 5
tsayAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sqlCmd = New SqlCommand("SELECT * FROM Table WHERE @d between convert( datetime, convert( varchar(10), FromDateTime , 120) , 120) AND dateadd(day, 1, convert ( datetime, convert( varchar(10), ToDateTime, 120), 120)) ", objSQLConn)
0
 
Joachim CarreinSr. Software DeveloperCommented:
try adding
sqlCmd.Parameters(2).DbType = DbType.Date
0
 
tsayAuthor Commented:
Perfect angelIII, tnx!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.