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 |
|
dipanshu.mittal
Starting Member
1 Post |
 Posted - 2012-09-30 : 13:15:41
|
| Dear All,Greetings!!!!Today I face a problem in Date Comparison please help me in out thanks in advance... problem mention below:-In my Table there is a Column Named DNCDATE, format of this Column is DateTime. when i pass a varchar type date then it will not compute any output. e.g DNCDATE>='29 Sep 2012' when i cast the column then it will give me output but it will take longer time to compute the result e.g. CAST( DNCDATE as DATE)>='29 Oct 2012'..Please guide me about the date compaison and help me out... |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-30 : 18:18:09
|
Use the YYYYMMDD format. So for 29, Sept 2012, you would use:DNCDATE>='20120929' YYYYMMDD is an unambiguous format - i.e., it will be interpreted correctly regardless of regional or language settings. Don't use the other form that you were trying to use (CASTing the date to a string) - it is inefficient, and not recommended for other reasons as well. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-01 : 11:33:25
|
quote:
Originally posted by dipanshu.mittal
Dear All,Greetings!!!!Today I face a problem in Date Comparison please help me in out thanks in advance... problem mention below:-In my Table there is a Column Named DNCDATE, format of this Column is DateTime. when i pass a varchar type date then it will not compute any output. e.g DNCDATE>='29 Sep 2012' when i cast the column then it will give me output but it will take longer time to compute the result e.g. CAST( DNCDATE as DATE)>='29 Oct 2012'..Please guide me about the date compaison and help me out...
seehttp://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-01 : 13:37:04
|
| Also worth pointing out the concept of SARGable (Search-Argument-able). When you wrap a function around a column in a table, SQL Server cannot properly optimize the execution plan and use the best indexes. http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargablehttp://ask.sqlservercentral.com/questions/1178/definition-of-sargable.htmlSimply reverse your statement and performance should return to normal:DNCDATE>=CONVERT(datetime,'29 Sep 2012')But I agree with the others that it is not a best practice to use this date format. |
|
|
|
|
|
|
Date Conversion Error