SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sql server datetime issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

308 Posts

Posted - 09/29/2012 :  17:07:33  Show Profile  Reply with Quote
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?

Edited by - jassie on 09/29/2012 17:08:05

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/29/2012 :  17:42:11  Show Profile  Visit webfred's Homepage  Reply with Quote
where yourColumn >= @Start_Date and yourColumn < dateadd(day,1,@Start_date)


Too old to Rock'n'Roll too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/01/2012 :  11:31:44  Show Profile  Reply with Quote
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/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

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000