datetime variable in where condition

Question

datetime variable in where condition

sudarsanan.kaliyamurthy

Old Hand

Points: 322
More actions
November 29, 2009 at 7:58 am

#213044

Declare @StartDate datetime,@Start_Date varchar(20)
Set @Start_Date='11/16/2009'
set @StartDate=cast(convert(datetime,@Start_Date,101) as datetime)
SELECT [Emp_Id],[Shift_Date] FROM [Emp_Shift_Details]Where Shift_Date=@StartDate;
When I run the above query without where condition i am receiving the result.
But where i check the date its not giving me any thing.
The problem must be with the format of the date i am using.
The below row is the sample data stored in the DB
9012009-11-16 12:00:00.000
Could some one help me how i need to use the datetime variable in where condition?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

your data in the table does not match your variable setting.

The value of your variable is 2009-11-16 00:00:00.000, but the value in your table is 12 hours ahead: 2009-11-16 12:00:00.000.

It depends on what you're looking for...

If you look for all values in your table with Shift_Date being within the day you specified you might want to query slightly different:

SELECT [Emp_Id],[Shift_Date]

FROM @t

WHERE Shift_Date >= @StartDate

AND Shift_Date < dateadd(dd,1,@StartDate)

Edit: Note: The query would benefit from an index on column Shift_Date, whereas a WHERE condition like "WHERE dateadd(dd,datediff(dd,0,Shift_Date),0) = @StartDate" would not.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sudarsanan.kaliyamurthy Old Hand Points: 322 More actions · Answer 2

Thanks for quick response.

That what I Expected.

Once again Thanks a lot.

LutzM SSC Guru Points: 107049 More actions · Answer 3

You're very welcome.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ShilpaSunil SSC-Addicted Points: 445 More actions · Answer 4

hi,

u can try this..

SELECT [Emp_Id],[Shift_Date] FROM [Emp_Shift_Details] Where

cast(convert(datetime,Shift_Date,101) as datetime) = @StartDate;

nigel. SSChampion Points: 11636 More actions · Answer 5

shilpas-1144056 (11/30/2009)
hi,
u can try this..
SELECT [Emp_Id],[Shift_Date] FROM [Emp_Shift_Details] Where
cast(convert(datetime,Shift_Date,101) as datetime) = @StartDate;

I would advise against this sort of thing in a WHERE clause.

See here for the reasons why

Nigel
How to post questions

armoredeagle SSC Eights! Points: 809 More actions · Answer 6

If you know the date is stored in the database with a noon hour, could you just add the 12 hours to your variable?

set @StartDate= dateadd(hh,12,cast(convert(datetime,@Start_Date,101) as datetime))

Would this not be better than performing date arithmetic in the Where clause?

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

tknight (12/1/2009)
If you know the date is stored in the database with a noon hour, could you just add the 12 hours to your variable?
set @StartDate= dateadd(hh,12,cast(convert(datetime,@Start_Date,101) as datetime))
Would this not be better than performing date arithmetic in the Where clause?

Sure... that'll work just fine... until someone starts adding dates with other times. It's better to always use the method that Lutz showed...it's sargable, it's fast, and it's good no matter what they do to the times in the future. I use that same method even when someone "guarantees" that the data will only contain midnight times (ie: "Whole" dates). It's saved my bacon more than once.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)