In a sql server 2008 r2 database, I have a question about using a datetime field. I have a column called start_date that is setup in the database as a datetime column. I want to setup a parameter in a stored procedure that will select the date I pick. Right now I have the parameter setup like the following: @Start_Date datetime = NULL.
when I pass a value like 01/17/2012 in the @Start_Date paramter, no records are selected. However I know there are records where the start_date in the database = 01/17/2012. It is probably due to the time part of the datetime value.
Thus can you tell me what I can do to pass the value of 01/17/2012 to the stored procedure and/or tell me what exact value I should pass to the stored procedure so the records I want are selected?
In a sql server 2008 r2 database, I have a question about using a datetime field. I have a column called start_date that is setup in the database as a datetime column. I want to setup a parameter in a stored procedure that will select the date I pick. Right now I have the parameter setup like the following: @Start_Date datetime = NULL.
when I pass a value like 01/17/2012 in the @Start_Date paramter, no records are selected. However I know there are records where the start_date in the database = 01/17/2012. It is probably due to the time part of the datetime value.
Thus can you tell me what I can do to pass the value of 01/17/2012 to the stored procedure and/or tell me what exact value I should pass to the stored procedure so the records I want are selected?
you've use >= and < logic that Fred showed you reason is when you pass a date value its interpretted as having timepart of 12:00 AM (start of the day)
so when you pass 01/17/2012 as the value and uses it in filter condition like
Field = @date
it returns only those records which has date value of eaxctly 01/17/2012 12:00 AM (created at midnight)
so to cover for the entire day you've to consider all records from
01/17/2012 12:00 Am till last millisecond before 01/18/2012 12:00 AM
which is why you use
Field >=@date AND field < DATEADD(dd,1,@date)
which translates to range '01/17/2012 12:00 AM' to last second before '01/18/2012 12:00AM'
also make sure you pass date values always in unambiguos format like 20120117
Sql server datetime issue
Posted - 09/29/2012 : 17:07:33
