SQL Server Forums
Profile | Register | 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
 New Topic  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
52309 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
326 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000