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)
 Date Conversion Error
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dipanshu.mittal
Starting Member

India
1 Posts

Posted - 09/30/2012 :  13:15:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/30/2012 :  18:18:09  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 10/01/2012 :  11:33:25  Show Profile  Reply with Quote

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...



see

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

lazerath
Constraint Violating Yak Guru

USA
343 Posts

Posted - 10/01/2012 :  13:37:04  Show Profile  Reply with Quote
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-sargable

http://ask.sqlservercentral.com/questions/1178/definition-of-sargable.html

Simply 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.

Edited by - lazerath on 10/01/2012 13:41:12
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.14 seconds. Powered By: Snitz Forums 2000