Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jassie
Constraint Violating Yak Guru
332 Posts |
 Posted - 2012-09-29 : 17:07:33
|
| 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/ortell me what exact value I should pass to the stored procedure so the records I want are selected? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-29 : 17:42:11
|
where yourColumn >= @Start_Date and yourColumn < dateadd(day,1,@Start_date)
Too old to Rock'n'Roll too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-01 : 11:31:44
|
quote:
Originally posted by jassie
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/ortell 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 youreason 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 likeField = @dateit 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 AMwhich is why you useField >=@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 20120117http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Sql server datetime issue