isdate() British/US date format problem

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

SQL Server 2008 Forums

Transact-SQL (2008)

isdate() British/US date format problem

Author

Topic

spinningtop
Starting Member

29 Posts

Posted - 2011-03-04 : 07:28:06

Hi

I was trying to update to an empty datetime column with data from a vchar column with the date format dd/mm/yyyy. The isdate function (as below) checking for valid dates works but it considers dates such as 25/06/2010 as invalid as its using the US date format. I guess this is because my sqls installation is set to US English, not sure. How can I adapt the code below so British dates are valid?

Thanks



Update <tablename>
Set fieldname2 = Case When isdate(fieldname1) = 1 
Then fieldname1
Else Null End

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-04 : 08:17:17

SET DATEFORMAT DMY
Update <tablename>
Set fieldname2 = Case When isdate(fieldname1) = 1
Then fieldname1
Else Null End

Jim

Everyday I learn something that somebody else already knew

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-03-04 : 08:40:52

far better to work with dates in ccyymmdd format. no ambiguities can occur.

spinningtop
Starting Member

29 Posts

Posted - 2011-03-04 : 08:59:12

Thanks.

I already tried this and I also tried the following.

ALTER LOGIN <username> WITH DEFAULT_LANGUAGE = British

The problem is then for some reason that my update query doesn't update any records at all ???

spinningtop
Starting Member

29 Posts

Posted - 2011-03-04 : 10:14:13

this seems to work.

regards


SET DATEFORMAT DMY
Update <tablename>
Set fieldname2 = Case When isdate(fieldname1) = 1 
Then THEN CONVERT(datetime, CAST(fieldname1 AS DATETIME), 103)
Else Null End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources