Error when converting date - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Error when converting date

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-03-31 : 00:31:48

Hi I have a column that is a character string.
Most of the values are date but some are different values.

I want to test for a date and if it is a date (ddmmyy) I want to convert it to a datetime (UK format)

I created this query

Select accountno, contsupref, coursecode, date ,
CASE WHEN isDate(convert(Datetime, [Date], 103)) = 1 THEN convert(Datetime, [Date], 103) ELSE NULL END as Date2
from
GMSM_TCI_MAIN.dbo.TCI_DELEGATE_EVENTS

but am getting errors saying

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-31 : 00:37:45

you use isdate() to check on the string not on datetime data type

change to


case when isdate([Date]) 1 then convert(datetime, [Date], 103) . . .

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-03-31 : 01:05:41

Thanks KHTAN,
I don't get the error anymore.
But unfortunately it's not converting to the correct date.
E.g. '301105' should be converting to
2005-11-30 00:00:00.000
...but it's going to
2030-11-05 00:00:00.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-31 : 01:22:03

quote:
Originally posted by icw

Thanks KHTAN,
I don't get the error anymore.
But unfortunately it's not converting to the correct date.
E.g. '301105' should be converting to
2005-11-30 00:00:00.000
...but it's going to
2030-11-05 00:00:00.000

style 103 is DD/MM/YYYY. In your case of 301105 it is DDMMYY.

Either you use stuff() to change to DD/MM/YYYY and then convert with style 103 or just swap the DD & YY to YYMMDD


declare @datestr varchar(10)
select	@datestr = '301105'
select	convert(datetime, right(@datestr, 2) + substring(@datestr, 3, 2) + left(@datestr, 2))

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-03-31 : 01:39:45

very much appreciated
thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources