(resolved) Convert varchar to date, w/ bad data

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 2000 Forums

Transact-SQL (2000)

(resolved) Convert varchar to date, w/ bad data

Author

Topic

ravl13
Starting Member

38 Posts

Posted - 2011-09-19 : 13:32:48

Greetings,

I have a table called 'largestdonation' which has an ID column (called "ID"), some irrelevant columns, and a "largestgiftdate" column which is actually of varchar datatype, and most of the data is stored in "DD/MM/YYYY" format. However, there is apparently a bad egg or two in the data, because when I try to change the column datatype to "date", the conversion fails.

I've made a test column of varchar data with a single varchar value of "1/24/2011", and when I changed the datatype to "date" in the table design wizard, I am able to save without problem, so that's why I believe there is bad data.

Is there a way for me to convert only the values in the column that are in the "DD/MM/YYYY" format, and set everything else to null? If a new column needs to be created in the table in order to place the "good dates", that's fine.

Thanks for your time,
-ravl13

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 13:34:33

Hello,

Have a look at ISDATE() function.

HTH.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 13:39:58

quote:
Originally posted by ehorn

Hello,

Have a look at ISDATE() function.

HTH.

ISDATE is not full reliable
Check below


DECLARE @i int,@j int,@k int,@l varchar(10)
SELECT @i =2003,@j =201010,@k=100110 ,@l='200110'
SELECT ISDATE(@i),ISDATE(@j ),ISDATE(@k),ISDATE(@l)

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

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 13:43:09

quote:
Originally posted by visakh16

quote:
Originally posted by ehorn

Hello,

Have a look at ISDATE() function.

HTH.

ISDATE is not full reliable
Check below

DECLARE @i int,@j int,@k int,@l varchar(10)
SELECT @i =2003,@j =201010,@k=100110 ,@l='200110'
SELECT ISDATE(@i),ISDATE(@j ),ISDATE(@k),ISDATE(@l)

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

I see. It may be 'reliable enough' (if such a thing exists) :) for the OP to identify his bad records. Worth a pass to see if it can pick off the bad records;


SELECT ID,[date column]
FROM largestdonation
WHERE ISDATE([date column]) = 0

If not, then I suppose a custom udf would be required.

ravl13
Starting Member

38 Posts

Posted - 2011-09-19 : 13:49:09

ISDATE() appears to work in my case

update t1
set datetest2 = t2.largestgiftdate
from largestdonation t1
join largestdonation t2 on t1.id = t2.id
where ISDATE(t2.largestgiftdate) = 1

--datetest2 is a column I created with datatype DATE

The update statement worked fine. Although perhaps isdate() is not always reliable as someone pointed out, it was sufficient in this case. Thanks to both posters!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 13:55:01

quote:
Originally posted by visakh16

quote:
Originally posted by ehorn

Hello,

Have a look at ISDATE() function.

HTH.

ISDATE is not full reliable
Check below

DECLARE @i int,@j int,@k int,@l varchar(10)
SELECT @i =2003,@j =201010,@k=100110 ,@l='200110'
SELECT ISDATE(@i),ISDATE(@j ),ISDATE(@k),ISDATE(@l)

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

Hello visakh16,

Thanks for the warning about this. Are you aware of any well-developed and robust udf's to replace ISDATE() for such a check?

I googled the topic but found few udf;s developed for such a check and most required a very specific input date format. This surprised me considering such an issue is known to exist.

quote:
Originally posted by ravl13

ISDATE() appears to work in my case

OP, Glad it was sufficient for your needs.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 14:04:16

quote:
Originally posted by ravl13

ISDATE() appears to work in my case

update t1
set datetest2 = t2.largestgiftdate
from largestdonation t1
join largestdonation t2 on t1.id = t2.id
where ISDATE(t2.largestgiftdate) = 1

--datetest2 is a column I created with datatype DATE

The update statement worked fine. Although perhaps isdate() is not always reliable as someone pointed out, it was sufficient in this case. Thanks to both!

Ok only problem is it relies upon your date format and language settings.
See


SET DATEFORMAT dmy
GO
SELECT ISDATE('13/02/2010'),ISDATE('1/24/2011')

SET DATEFORMAT mdy
GO
SELECT ISDATE('13/02/2010'),ISDATE('1/24/2011')

SET LANGUAGE us_english
GO
SELECT ISDATE('13/02/2010'),ISDATE('1/24/2011')

SET LANGUAGE British
GO
SELECT ISDATE('13/02/2010'),ISDATE('1/24/2011')

Also since you've dates stored in both formats within same field how do you determine whether date value like

07/05/2011 is 5th of July 2010 or 7th of May 2010
it can be either of above based on whether it was stored in correct (DD/MM/YYYY) format or wrong format (MM/DD/YYYY). So you might end up completely misinterpreting date value which affects your results. Thats is what its always better to use proper datatype for columns (in this case datetime) and also always pass dates in unambiguos standard format ie YYYYMMDD

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

ravl13
Starting Member

38 Posts

Posted - 2011-09-19 : 14:47:01

I'm working under the assumption that all dates are US based, since the records are US only. I also checked that "2003-09-30" came up equivalent to 'sep 30, 2003', and it did.

Fortunately I don't have to worry about mixed countries :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 14:48:46

quote:
Originally posted by ravl13

I'm working under the assumption that all dates are US based, since the records are US only. I also checked that "2003-09-30" came up equivalent to 'sep 30, 2003', and it did.

Fortunately I don't have to worry about mixed countries :)

you're safe as long as your app is region specific.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources