Results 1 to 2 of 2

Thread: Selecting NULL Date Values

  1. #1
    Join Date
    Oct 2010
    Posts
    1

    Selecting NULL Date Values

    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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    What's data type of u.suspended and u.approved? Tried convert them to char in the query?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •