Converting string to datetime

Hi,

I'm in a bit of a tricky situation. I'm upgrading and existing application (VB.NET 05 and sql server 2000). One of the tables has field having datatype varchar(20) but actually storing dates. From different parts of the application the datetime values are saved basically in three formats.

1. 2004/11/26 00:00:00

2. Nov 25 2004 12:00AM
3. 24/11/2004

The problem is I need to run a datediff to calculate a date difference. I can't get my head around to convert all the three types to one data type using a sql.

Really appritiate if some one can help me out on this.

Regards,

Vije

If your data is exactly the three formats you provided, then this approach should work for you:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
MyDate nvarchar(30),
MyNewDate datetime
)

INSERT INTO @.MyTable ( MyDate ) VALUES ( '2004/11/26 00:00:00' )
INSERT INTO @.MyTable ( MyDate ) VALUES ( 'Nov 25 2004 12:00AM' )
INSERT INTO @.MyTable ( MyDate ) VALUES ( '24/11/2004' )

UPDATE @.MyTable
SET MyNewDate = CASE isdate( MyDate )
WHEN 1 THEN cast( MyDate AS datetime )
ELSE convert( datetime, MyDate, 103 )
END
SELECT *
FROM @.MyTable

RowID MyDate MyNewDate
-- - -
1 2004/11/26 00:00:00 2004-11-26 00:00:00.000
2 Nov 25 2004 12:00AM 2004-11-25 00:00:00.000
3 24/11/2004 2004-11-24 00:00:00.000

|||

Vije:

You might be able to get by with something like this:

Code Snippet

select theDt,
case when charindex(':', theDt) <> 0
and isDate(theDt) = 1
then convert(datetime, theDt)
when charindex(':', theDt) = 0
and isDate(theDt) = 0
and isDate
( parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
) = 1
then parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
end as convertedDT
from ( select '2004/11/26 00:00:00' as theDT union all
select 'Nov 25 2004 12:00AM' union all
select '24/11/2004' union all
select 'invalid'
) a
/*
where isDate(theDt) = 1
or ( charindex(':', theDt) = 0
and isDate(theDt) = 0
and isDate
( parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
) = 1
)
*/

/*
theDt convertedDT
-
2004/11/26 00:00:00 2004-11-26 00:00:00.000
Nov 25 2004 12:00AM 2004-11-25 00:00:00.000
24/11/2004 2004-11-24 00:00:00.000
invalid NULL
*/

If you don't want the invalid data to appear just uncomment the WHERE clause (that is shown in red).

As an aside, you ought to consider converting this column to a DATETIME data type. These kinds of problems will only grow until you resolve the real problem here -- which is a design problem.

|||

In one single update statement you can't do it..

But the following batch may help you.

Code Snippet

Create Table #datedata (

[Dates] Varchar(20)

);

Insert Into #datedata Values('2004/11/26 00:00:00');

Insert Into #datedata Values('Nov 25 2004 12:00AM');

Insert Into #datedata Values('24/11/2004');

Set DateFormat DMY

Update

#datedata

Set

Dates = Convert(varchar,cast(Dates as datetime) , 120) -- Finaly converted as ANSI Format

Where

Isdate(Dates) = 1

Set DateFormat YMD

Update

#datedata

Set

Dates = Convert(varchar,cast(Dates as datetime) ,120) -- Finaly converted as ANSI Format

Where

Isdate(Dates) = 1

Select * From #dateData

|||

Woooh .. We all 3 given unique and different solution.... . I like Arnie's solution, Kent you are really hard worker..