Fixing date column with multiple forms of dates

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

Fixing date column with multiple forms of dates

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-22 : 20:21:08

I have a column with 2 different forms of dates.

One form is like so...

13 | 8-2008
20 | 8-2008
17 | 8-2008

I sucessfully converted this into datetime format like so...

select convert(datetime, replace(checkindate, ' | ', '-'), 103) as checkindate, etc etc

but now I have new entries that look like this.

Aug. 30, 2008 thru Sep. 01, 2008
Aug. 23, 2008 thru Aug. 25, 2008
Sep. 20, 2008 thru Sep. 22, 2008

in the same column.

I tried this case expression...

select checkindate = case when len(checkindate) < 12 then convert(datetime, replace(checkindate, ' | ', '-'), 103) else 'convert the other form of date inte datetime', etc etc

But I can't even get the first part of the case to work. Because I tried to make it just display some static string and it kept saying.

Conversion failed when converting datetime from character string.

help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-22 : 21:20:22

run this and see if all the date are in that format. Some of them are not in that format that is causing it to failed


Select checkindate
from   yourtable
where  len(checkindate) < 12

beside checking for len, you might want to check for existance of the '|' character

KH
[spoiler]Time is always against us[/spoiler]

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-23 : 15:15:55

quote:
Originally posted by khtan

run this and see if all the date are in that format. Some of them are not in that format that is causing it to failed

Select checkindate
from yourtable
where len(checkindate) < 12

beside checking for len, you might want to check for existance of the '|' character

KH
[spoiler]Time is always against us[/spoiler]

how do you do that?

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-23 : 15:24:59

I did this

select hotel, ltrim(rtrim(roomtype)) as roomtype ,
convert(datetime, replace(checkindate, ' | ', '-'), 103) as checkindate,
SUBSTRING(roomrate,CASE WHEN CHARINDEX('$',roomrate)>0 THEN CHARINDEX('$',roomrate)+2 ELSE 1 END,CASE WHEN CHARINDEX('-',roomrate)>0 THEN (CHARINDEX('-',roomrate)-3)-CHARINDEX('$',roomrate) ELSE LEN(roomrate) END) as roomrate,
dateadd(dd, datediff(dd, 0, executiontime), 0) as executiontime, agentname, id_num, inputvalue
from morganshotel
where checkindate like '%|%'

and it worked but when I tried this...

select hotel, ltrim(rtrim(roomtype)) as roomtype ,
checkindate = case when checkindate like '%|%' then
convert(datetime, replace(checkindate, ' | ', '-'), 103) else 'unknown' end,
SUBSTRING(roomrate,CASE WHEN CHARINDEX('$',roomrate)>0 THEN CHARINDEX('$',roomrate)+2 ELSE 1 END,CASE WHEN CHARINDEX('-',roomrate)>0 THEN (CHARINDEX('-',roomrate)-3)-CHARINDEX('$',roomrate) ELSE LEN(roomrate) END) as roomrate,
dateadd(dd, datediff(dd, 0, executiontime), 0) as executiontime, agentname, id_num, inputvalue
from morganshotel

this query above gives the convert string error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-24 : 13:52:40

What was the error message?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources