How to convert date time, rtn null if 12/31/1899 2

[glthornton]

Hi Everyone,
I'm using SQL 2000 server, Win 2003 server. I am trying to convert a datetime field into a regular date but I need the result to provide me a null when if the date returned is 12/31/1899. Here is what I have so far:

 SELECT patid,acctpriority,accounttype, 
   TYPE=CASE
     WHEN acctpriority = '1' and accounttype = '1' THEN 'Pri Health Insurur:'
     WHEN acctpriority != '1' and accounttype = '1' THEN 'Sec Health Insurur:'
     WHEN acctpriority = '1' and accounttype = '2' THEN 'Pri Worker Comp:'
     WHEN acctpriority != '1' and accounttype = '2' THEN 'Sec Worker Comp:'
     WHEN acctpriority = '1' and accounttype = '3' THEN 'Pri Attorney Insurer:'
     ELSE 'Sec Attorney Insurer:'
     END,convert(nvarchar,StartDate,101) as Start_Date,convert(nvarchar,Enddate,101) as End_Date,displayname
 FROM PatientInsuranceView
 ORDER BY patid,acctpriority, accounttype

With results like this:

300	1	1	Pri Health Insurur:	02/01/1990	12/30/1899	MCARE-Medicare B PO Box 7777
400	1	1	Pri Health Insurur:	12/30/1899	12/30/1899	MCARE-Medicare B PO Box 7777
400	2	1	Sec Health Insurur:	12/30/1899	12/30/1899	AARP- AARP PO Box 740819
500	1	1	Pri Health Insurur:	12/30/1899	12/30/1899	BCBS-Anthem BCBS **YGM (only)** POB 533
900	1	1	Pri Health Insurur:	06/01/2003	12/30/1899	MCAID-Primary Care Plus POB 777
1800	1	1	Pri Health Insurur:	05/01/1999	12/30/1899	MCARE-Medicare B PO Box 7777

What I am looking for is this for results:

300	1	1	Pri Health Insurur:	02/01/1990	        	MCARE-Medicare B PO Box 7777
400	1	1	Pri Health Insurur:	          	        	MCARE-Medicare B PO Box 7777
400	2	1	Sec Health Insurur:	          	        	AARP- AARP PO Box 740819
500	1	1	Pri Health Insurur:	          	        	BCBS-Anthem BCBS **YGM (only)** POB 533
900	1	1	Pri Health Insurur:	06/01/2003	        	MCAID-Primary Care Plus POB 777
1800	1	1	Pri Health Insurur:	05/01/1999	        	MCARE-Medicare B PO Box 7777

I cannot locate anything on the web or within Tek-Tips that shows me how to do this. Can anyone assists?

Thank you,
glthornton

 

[r937]

change this --

CONVERT(NVARCHAR,StartDate,101) AS Start_Date

to this --

CASE WHEN StartDate = '12/31/1899' THEN NULL
ELSE CONVERT(NVARCHAR,StartDate,101)
END AS Start_Date


r937.com | rudy.ca

 

[jbenson001]

Use another CASE statment and if it's 12/30/1899, then display NULL else display convert(nvarchar,StartDate,101)

 

[glthornton]

You folks are great! I should have thought of that myself. Thank you again!

glthornton

 

[r937]

jbenson, i'm jealous, you got the star for describing the CASE expression i wrote, and you even got the date wrong




r937.com | rudy.ca

 

[r937]

oops, i spoke too soon, i see i got one too

thanks!!

r937.com | rudy.ca

 

[jbenson001]

Rudy,

Actually , I posted right after you and didn't see your post. Not trying to steal your thunder

I actually got the date right according to his test data. So, are you we both right, or wrong? LOL