[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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

0
Jeff S
Asked:
Jeff S
2 Solutions
 
Scott PletcherSenior DBACommented:
A date of '' is 1900-01-01, since that is the default date in SQL Server.
0
 
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:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now