May 1, 2009 at 7:44 am
The following query errors out when I try to import bad dates from a temporary table "b" to the permanet table "a". I located the problem date and it is related to a bad date that is being sent to me from a flat file "0200-07-30" and I would like to handle the bad date and change it to ' ' or null.
I tried to do something like but I cant seem to get the syntax correct
CASE b.[LAST_DAY_WORKED]) = CAST(LEFT(b.[LAST_DAY_WORKED]) AS INT) < 1900
ELSE
(Cast(nullif(a.[LAST_DAY_WORKED], '')as datetime) IS NULL and Cast(nullif(b.[LAST_DAY_WORKED], '') as datetime) IS NULL
or a.[LAST_DAY_WORKED] = b.[LAST_DAY_WORKED])
END
I would like to handle this bad date by using a case statement or whatever suggestion you may have.
Thanks for your help.
[code]
DELETE b
FROM dbo.ADIM_ASSOCIATE_CONTRACTOR_RawImport b
INNER JOIN dbo.ADIM_ASSOCIATE_CONTRACTOR a
ON a.[EMPLOYEE_NUMBER] = b.[EMPLOYEE_NUMBER
AND a.[ASSOCIATE_CONTRACTOR_FLAG] = b.[ASSOCIATE_CONTRACTOR_FLAG]
-------
-------more compare
-------
-------
AND (Cast(nullif(a.[LAST_DAY_WORKED], '')as datetime) IS NULL and Cast(nullif(b.[LAST_DAY_WORKED], '') as datetime) IS NULL
or a.[LAST_DAY_WORKED] = b.[LAST_DAY_WORKED])
[/code]
May 1, 2009 at 7:50 am
Hi David
Did you try ISDATE?
DECLARE @t TABLE (MyDate VARCHAR(100))
INSERT INTO @t
SELECT '0200-07-30'
UNION ALL SELECT '2009-01-01'
UNION ALL SELECT '2008-02-29'
UNION ALL SELECT '2009-02-29'
SELECT *
FROM @t
WHERE ISDATE(MyDate) = 1
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
May 1, 2009 at 7:52 am
Would the following check help (for details please see BOL)?
CASE
WHEN ISDATE(b.LAST_DAY_WORKED) = 1 THEN b.LAST_DAY_WORKED
ELSE ''
END
May 1, 2009 at 7:56 am
Hi Flo,
looks like we're running a competition today...
You beat me on that one :crying:
Obviously you're not enjoying the Holiday the old-fashioned way... Neither do I 😛
May 1, 2009 at 8:00 am
lmu92 (5/1/2009)
looks like we're running a competition today...You beat me on that one :crying:
Next thread will be yours 😛
Obviously you're not enjoying the Holiday the old-fashioned way... Neither do I 😛
No, I'm not jumping drunken around a maypole 😀
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
May 1, 2009 at 2:28 pm
Yes isdste fianlly worked. Thank you.
(cast(CASE when ISDATE(b.[LAST_DAY_WORKED]) = 1 Then (b.[LAST_DAY_WORKED]) ELSE NULL END as datetime) = cast(CASE when ISDATE(a.[LAST_DAY_WORKED]) = 1 Then (a.[LAST_DAY_WORKED]) ELSE NULL END as datetime)
or (nullif(a.[LAST_DAY_WORKED], '') is null and nullif(b.[LAST_DAY_WORKED], '') is null))
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply