Searching and replacing chars - SQL Server Forums

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

Searching and replacing chars

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-06 : 05:59:29

Hi

I have a varchar(10) field which should be in the form 'dd-mm-yyyy' but some of the field has been inputted as 'd-mm-yyyy' or 'dd-m-yyyy' or even as 'd-m-yyyy'

Is there an easy way of checking and changing (by adding mising digits?) to make the filed complete either as 'dd-mm-yyy' or 'mm-dd-yyyy' (whichever way it should have been inputted)?

The data has come across from a different database (external) I have no control over input of data and I need to clean it up.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 06:01:16

why do you have varchar field for storing datetime values?
the conversion can be done with the help of CONVERT function. Have a look at syntax in books online.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-06 : 06:27:14

I have tried this:-

DECLARE @d datetime, @id int, @d1 datetime, @id1 int

SELECT @d1 = '1999-4-1' --format = yyyy-m-d

SELECT CONVERT(varchar(11),@d1,113)
SELECT CONVERT(varchar(11),@d1,112)

SELECT @d1 = '4-11-1999' --format = m-d-yyyy

SELECT CONVERT(varchar(11),@d1,113)
SELECT CONVERT(varchar(11),@d1,112)

SELECT @d1 = '1999-24-1' --format = yyyy-d-m

SELECT CONVERT(varchar(11),@d1,113)
SELECT CONVERT(varchar(11),@d1,112)

SELECT @d1 = '4-13-1999' --format = m-d-yyyy

SELECT CONVERT(varchar(11),@d1,113)
SELECT CONVERT(varchar(11),@d1,112)
------------------------------------------------------------------
and my results are:-

-----------
01 Apr 1999

(1 row(s) affected)

-----------
19990401

(1 row(s) affected)

-----------
11 Apr 1999

(1 row(s) affected)

-----------
19990411

(1 row(s) affected)

Msg 242, Level 16, State 3, Line 15
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

-----------
11 Apr 1999

(1 row(s) affected)

-----------
19990411

(1 row(s) affected)

-----------
13 Apr 1999

(1 row(s) affected)

-----------
19990413

(1 row(s) affected)
--------------------------------------------------------------------------------------

to give possible scenarios, but it fails on one as you can see, how do I get around that failing scenario?

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-06 : 06:28:18

The reason I have a varchar is because I can't import due to failing as above.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 06:34:11

But this will have a problem because if a value comes, because it has both formats appearing (ddmmyyyy & mmddyyyy) it cant distinguish which format it should interpret. so unless you restrict input to be of any one value it would be problem to handle it.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-06 : 06:45:50

Thanks I was hoping there would be some way of choosing if it was in the wrong format to use another conversion instead,

something along the lines of:-

If IsDate(SELECT CONVERT(varchar(11),@d1,113)) then
--(convert using this way)

Else

--(convert the other way!)

End if

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 06:57:12

ok. here is a way to do it

SELECT case when isdate(val)=1 then convert(datetime,val,101) else convert(datetime,val,103) end
from
(
select '1-15-2008' as val union all
select '11-10-2008' union all
select '25-5-2008' union all
select '1-25-2008' union all
select '1 Apr 2008' union all
select '1/11/2008' union all
select '20/01/2008' 
)t

output
---------------------------------------------
2008-01-15 00:00:00.000
2008-11-10 00:00:00.000
2008-05-25 00:00:00.000
2008-01-25 00:00:00.000
2008-04-01 00:00:00.000
2008-01-11 00:00:00.000
2008-01-20 00:00:00.000