Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

332 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
8781 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
52326 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  
 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.05 seconds. Powered By: Snitz Forums 2000