Microsoft, SQL, SQL 2005 - Query Assistance

I need help recoding this one segment .....

I am currently getting back a '1900-01-01 00:00:00.000' when the ELSE IsNull(pi.birthdate,'') should have reported back nothing. Can someone assist me?

'128.Insured1DOB' = 
		CASE  WHEN COALESCE(pi.InsuredSameAsPatient,1) = 1 THEN pp.birthdate
		WHEN COALESCE(pi.InsuredSameAsGuarantor,1) = 1 THEN g.birthdate
		ELSE IsNull(pi.birthdate,'') END

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
CASE  WHEN COALESCE(pi.InsuredSameAsPatient,1) = 1 THEN CONVERT(VARCHAR(10),pp.birthdate,120)
            WHEN COALESCE(pi.InsuredSameAsGuarantor,1) = 1 THEN CONVERT(VARCHAR(10),g.birthdate,120)
            ELSE IsNull(pi.birthdate,'') END
0
 
Scott PletcherSenior DBACommented:
A date of '' is 1900-01-01, since that is the default date in SQL Server.
0
 
Scott PletcherSenior DBACommented:
Such as this:
SELECT CAST('' AS DATETIME)

To leave it truly blank, you will need to force the dates to varchar so that an empty string can be shown.  For example:
CASE  WHEN COALESCE(pi.InsuredSameAsPatient,1) = 1 THEN CONVERT(VARCHAR(10), pp.birthdate, 101)
		WHEN COALESCE(pi.InsuredSameAsGuarantor,1) = 1 THEN CONVERT(VARCHAR(10), g.birthdate, 101)
		ELSE IsNull(CONVERT(VARCHAR(10), pi.birthdate, 101), '') END

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Aneesh RetnakaranDatabase AdministratorCommented:
Since the case statement is returning datetime values in case  birthDate is null, sql will try to convert the value '' to datetime implicitly yielding the default datetime valuw od 1900-01-01

SELECT CAST('' AS DATETIME)
0
 
MikeTooleCommented:
You should put a Null in the Birdate Field -  I presume that it's a data datatype? - these are sotred internally as numbers and can't contain a blank.
I guess your code actually has the effect of storing a zero . Data fields store the data as an offset in days from 1900-01-01, so tha's what zero signifies.
0
 
Jeff SAuthor Commented:
Thanks a million ..... just the result I needed and awesome explanations! Thanks to all who helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.