Error With Date Convert - 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 2005 Forums

Transact-SQL (2005)

Error With Date Convert

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-10-14 : 09:44:41

Hello,

I'd like some help please if possible.

I have a table that has a Holiday Date column in it. This column isn't actually stored as a date though, it's stored as a char(10) and this can't be changed.

I've then created a view and added a column called 'Week Commencing' using the code below :


DATEADD(DD, 1 - DATEPART(DW, HolidayDate), HolidayDate)

I've also cast the Holiday Date column as a datetime.

When I run this view it runs fine, but when I scroll down the end using SQL EM I get the error message below :


[Microsoft][ODBC SQL Server Driver][SQL Server] The conversion of a char data type to a datetime data type resulted in an out of range datetime value

Does anyone know why this is, and how I can fix it ?
I thought it may be a problem with one of the values in the Holiday Date field, but everything seems to be in order.

Is there any way I can track where this error is coming from ?

Thank you in advance.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-10-14 : 09:51:24

check out this

http://www.prezzatech.com/kb/articles/kb-1005-conversion_char_to_datetime.asp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-14 : 09:52:30

select * from yourtable where isdate(HolidayDate) = 0
shows bad dates

Webfred

Planning replaces chance by mistake

Jonny1409
Posting Yak Master

133 Posts

Posted - 2008-10-14 : 09:59:25

Thank you so much to you both, I've found the problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 11:17:22

quote:
Originally posted by webfred

select * from yourtable where isdate(HolidayDate) = 0
shows bad dates

Webfred

Planning replaces chance by mistake

not always. ISDATE() is not relaible. try the below

ISDATE('2007')

so you might need to add additional checks to make sure the date ia valid.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-14 : 12:06:29

Ok Visakh, you are right

. But it is helpful as a first step to isolate the bad data.

Webfred

Planning replaces chance by mistake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 12:44:07

quote:
Originally posted by webfred

Ok Visakh, you are right . But it is helpful as a first step to isolate the bad data.

Webfred

Planning replaces chance by mistake

it definitely is. thats why i told you might require additional checks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-15 : 03:10:13

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources