I'm not a DBA and I'm sure this is something simple, but I'm just tired of dealing with it. I recently upgraded a client's application DB from MSDE 2000 to SQL Express 2008. A custom report I wrote is throwing errors trying to select fields with NULL date values. The problem is the 'DECISION_DATE' in SELECT. This query ran fine in MSDE 2000. Please excuse my sloppiness:
Code:
SELECT
g.loan_num AS 'LOAN_NUM',
g.borrow_ln AS 'BORR_LAST_NAME',
ui.last_name AS 'UNDERWRITER',
u.processor AS 'PROCESSOR',
g.app_date AS 'DATE_SUBMITTED',
u.received AS 'UW_RECEIVED',
'DECISION_DATE' =
CASE
WHEN u.suspended IS NULL AND u.approved IS NULL THEN NULL
WHEN u.suspended IS NULL THEN u.approved
WHEN u.approved IS NULL THEN u.suspended
WHEN u.suspended > u.approved THEN u.suspended
WHEN u.approved > u.suspended THEN u.approved
ELSE 'ERROR'
END,
'STATUS' =
CASE
WHEN u.suspended IS NULL AND u.approved IS NULL THEN 'NO DECISION'
WHEN u.suspended IS NULL THEN 'APPROVED'
WHEN u.approved IS NULL THEN 'SUSPENDED'
WHEN u.suspended > u.approved THEN 'SUSPENDED'
WHEN u.approved > u.suspended THEN 'APPROVED'
ELSE 'ERROR'
END,
u.clear_to_close AS 'DATE_CTC',
DATEDIFF(d,g.app_date,u.received) AS 'DAYS_TO_SUBMIT',
DATEDIFF(d,u.received,COALESCE(u.suspended,u.approved)) AS 'DAYS_TO_DECISION',
DATEDIFF(d,g.app_date,u.clear_to_close) AS 'DAYS_TO_CLEAR'
FROM
DMD_Dataprecision.dbo.doc d,
DMD_Dataprecision.dbo.gen g,
DMD_Dataprecision.dbo.und u,
DMD_Dataprecision.dbo.userinfo ui
WHERE
u.file_id = g.file_id AND
g.file_id = d.file_id AND
u.underwrtr = ui.employ_id AND
(
u.processor = 'USER, ONE' OR
u.processor = 'USER, TWO' OR
u.processor = 'USER, THREE' OR
u.processor = 'USER, FOUR'
) AND
(
u.underwrtr <> '008' OR
u.denied IS NOT NULL OR
(
(d.drawn IS NULL) OR
(u.approved < d.drawn) OR
(u.suspended < d.drawn)
)
)
ORDER BY
BORR_LAST_NAME ASC
Thank you for your time. Any help or recommendations are much appreciated =)
Oh ya, here's the error:
Code:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.