isdate(date) = 0 in varchar date

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

General SQL Server Forums

New to SQL Server Programming

isdate(date) = 0 in varchar date

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-02-08 : 14:52:44

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?

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 15:31:19

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-08 : 15:50:54

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

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-02-08 : 16:01:39

@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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 16:21:46

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

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-02-09 : 07:01:49

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-09 : 07:32:50

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.

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-02-09 : 08:14:45

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-09 : 08:23:11

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/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-09 : 08:28:11

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

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-02-09 : 12:01:55

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"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-09 : 12:27:55

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/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-09 : 16:09:10

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.

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-02-10 : 04:08:05

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-10 : 06:40:22

You are very welcome - glad you were able to resolve it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 00:50:09

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources