Date Time handling in where clause

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date Time handling in where clause

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-22 : 03:15:03

Hi,
i pass @dateRange as varchar say '2010-03-03'

i have where condition where this @dateRange validates

select * from table
where orderDateRange = dateRange
---------orderDateRange is datetime "2010-03-03 07:44:54.137"
this doesnt validate

so i used like this
where CAST(CONVERT(VARCHAR,orderDateRange ,106) AS DATETIME) = CAST('2010-03-03' AS DATETIME)
this works but is this correct
any idea. thanks in advance

Iam a slow walker but i never walk back

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 03:22:33

although you will get the required result, but it is more efficient if you can do this


where orderDateRange >= @dateRange
and   orderDateRange <  dateadd(day, 1, @dateRange)

KH
[spoiler]Time is always against us[/spoiler]

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-22 : 03:28:54

Thanks it works fine.

Iam a slow walker but i never walk back

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 11:53:16

quote:
Originally posted by dineshrajan_it

Hi,
i pass @dateRange as varchar say '2010-03-03'

i have where condition where this @dateRange validates

select * from table
where orderDateRange = dateRange
---------orderDateRange is datetime "2010-03-03 07:44:54.137"
this doesnt validate

so i used like this
where CAST(CONVERT(VARCHAR,orderDateRange ,106) AS DATETIME) = CAST('2010-03-03' AS DATETIME)
this works but is this correct
any idea. thanks in advance

Iam a slow walker but i never walk back

its always better to specify length when you cast to character data or numeric data

see

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-23 : 02:33:12

Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-23 : 02:40:11

Yes i got it. its better to use length

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources