March 17, 2008 at 11:50 am
I am working on a DTS package in SQL2000. I want to read a column char(8) that contains dates as a string 'yyyymmdd'. the destination is a column of datetime data type. When I use cast(datex as datetime) AS DATEX in my query to define the source in my DTS I get the error that certain values result in in an out of range error. In trying I figured out that it is the value '00000000' in certain rows that cause the error. How do I deal with this???
March 17, 2008 at 11:59 am
Trying to cast '00000000' will result in an error since it can't be converted to a date - so you will need to suppress them somehow or replace them with something else denoting an "invalid date" in your model.
Try something like
...
cast(case when datex='00000000' then null else datex end as datetime)
...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 17, 2008 at 12:13 pm
NullIf will be faster than a case statement by a small margin.
select cast(NullIf(datex, '00000000') as datetime)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 12:25 pm
That worked but I still get the out of range message on some records soooooooooo .. is there a syntax I can use that tries the conversion but if it fails then use NULL. So that any 'bad' value is covered??
Your help is greatly apreciated
March 17, 2008 at 2:49 pm
Use the IsDate function in your Where clause? It'll be slow, but it'll get the job done.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply