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
 General SQL Server Forums
 New to SQL Server Programming
 isdate(date) = 0 in varchar date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

218 Posts

Posted - 02/08/2013 :  14:52:44  Show Profile  Reply with Quote
I have a column date in varchar(30) and format dd/mm/yyyy datattype as below

date
01/01/2012
02/02/2012
03/03/2012
04/04/2012
05/05/2012
06/06/2012
07/06/2012
23/06/2012
24/06/2012
25/06/2012
26/06/2012


When I write a query like below


select date from table1
where isdate(date) = 0


I get this output. Does that mean sqlserver cannot understand they are dates?


23/06/2012
24/06/2012
25/06/2012
26/06/2012


If I change the code like this,

select convert(varchar(8),convert(datetime,date,103),112) as date
from table1


Still I get this output. It is clearly in date format so I wonder why sqlserver cannot understand they are dates.


20120623
20120624
20120625
20120626

Edited by - learning_grsql on 02/08/2013 14:54:37

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 02/08/2013 :  15:31:19  Show Profile  Reply with Quote
This is because of the DATEFORMAT setting. http://msdn.microsoft.com/en-us/library/ms189491.aspx

Try this code and you will see what I mean. Some of the dates such as 04/04/2012 are returning true because they are valid dates in mm/dd/yyyy format and in dd/mm/yyyy format.
SET DATEFORMAT dmy
SELECT ISDATE('26/06/2012') -- returns 1

SET DATEFORMAT mdy
SELECT ISDATE('26/06/2012') -- returns 0
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 02/08/2013 :  15:50:54  Show Profile  Reply with Quote
One of the many reasons why best practices says store date information in one of the date datatypes, store numbers in an appropriate number datatype, etc.

Be One with the Optimizer
TG
Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 02/08/2013 :  16:01:39  Show Profile  Reply with Quote
@JamesK and TG. Thank you.

Can you tell me why the output was "20120623", "20120624" in my second code as I believe this was unambigious format?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 02/08/2013 :  16:21:46  Show Profile  Reply with Quote
You were only converting to a varchar(8). If you then do an ISDATE on it, it should return 1.
--returns 1 in both cases
SET DATEFORMAT dmy
select ISDATE(convert(varchar(8),convert(datetime,'26/06/2012',103),112) )
SET DATEFORMAT mdy
select ISDATE(convert(varchar(8),convert(datetime,'26/06/2012',103),112) )
Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 02/09/2013 :  07:01:49  Show Profile  Reply with Quote
Thank you James
I have one question. How can I make my sql server understand that always my date format would be "dmy"?

I understand for a particular query I can set the format using your code "set dateformat dmy" at the beginning.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 02/09/2013 :  07:32:50  Show Profile  Reply with Quote
DATEFORMAT goes with the language. So if your language is US-English, the default DATEFORMAT is mdy. There is no way that I know of to specify DATEFORMAT independent of the language. You can see the various languages and their DATEFORMAT setting using sp_helplanguage.

Changing the language permanently (which needs to be done at server level) is probably not something you want to do. You can set the language for a session without affecting the server setting, just like you can change DATEFORMAT setting for a session. But if you have to mess with the language at the session level to effect a change in the DATEFORMAT setting, you might as well leave the language alone and set the DATEFORMAT to whatever you prefer at the session level.
Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 02/09/2013 :  08:14:45  Show Profile  Reply with Quote
Thanks James Again.

Changing default language/region to UK doesn't work either. Though in regional settings, it shows "dmy" format.

select * from sp_helplanguage brings error. How to access this then?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/09/2013 :  08:23:11  Show Profile  Reply with Quote
quote:
Originally posted by learning_grsql

Thanks James Again.

Changing default language/region to UK doesn't work either. Though in regional settings, it shows "dmy" format.

select * from sp_helplanguage brings error. How to access this then?



see

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

always try to use unambiguos format for dates

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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 02/09/2013 :  08:28:11  Show Profile  Reply with Quote
quote:
Originally posted by learning_grsql

Thanks James Again.

Changing default language/region to UK doesn't work either. Though in regional settings, it shows "dmy" format.

select * from sp_helplanguage brings error. How to access this then?

It's a stored proc, so use "exec sp_helplanguage". You can find what your current language is using "SELECT @@LANGUAGE".
Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 02/09/2013 :  12:01:55  Show Profile  Reply with Quote
Thanks visakh and James.

@Visakh, I remember you have remembered me this several times and I will definitely follow it for my future tables/databases. Moreover, I'm here to clarify how sql server date works and it will help me in the future to avoid date related errors .And I always like to read your blogs.

@James, the code "select @@Language" gets output "us english". However, in regional settings in computer it shows "UK English"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/09/2013 :  12:27:55  Show Profile  Reply with Quote
quote:
Originally posted by learning_grsql

Thanks visakh and James.

@Visakh, I remember you have remembered me this several times and I will definitely follow it for my future tables/databases. Moreover, I'm here to clarify how sql server date works and it will help me in the future to avoid date related errors .And I always like to read your blogs.

@James, the code "select @@Language" gets output "us english". However, in regional settings in computer it shows "UK English"



Regional settings of computer is not same as sql server language setting

for changing that use

SET LANGUAGE British

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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 02/09/2013 :  16:09:10  Show Profile  Reply with Quote
If you are in UK and if the server language is set to us_english, I am suspecting that that might have been done for a purpose (or may have been a mistake). You could change the server level setting to British, but I would not recommend that unless you do a lot of testing. The reason is that all the existing users probably have their default language set as us_english, and so you would need to go through all of them and change. Then there may be code and databases that have us_english; you will need to review all of that as well to make appropriate changes and test.

So at this point, unless you are starting with a clean slate, the best option would be:

a) Insist that only ISO format is used for working with date literals.
b) If there is really a need to use a different DATEFORMAT, do it at the session level.
Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 02/10/2013 :  04:08:05  Show Profile  Reply with Quote
I changed it to British and I believe in our case it will not affect anything. So far it's fine and I'm happy.

Thanks Visakh and James. I take your advice.

Edited by - learning_grsql on 02/10/2013 04:09:07
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 02/10/2013 :  06:40:22  Show Profile  Reply with Quote
You are very welcome - glad you were able to resolve it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/11/2013 :  00:50:09  Show Profile  Reply with Quote
quote:
Originally posted by learning_grsql

I changed it to British and I believe in our case it will not affect anything. So far it's fine and I'm happy.

Thanks Visakh and James. I take your advice.


welcome

I would recommend writing server independent code and avoid overriding default server settings as much as possible to make code portable

see an example below

http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html



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

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.14 seconds. Powered By: Snitz Forums 2000