Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Microsoft, SQL, SQL 2005 - Query Assistance

Posted on 2007-11-16
Medium Priority
246 Views
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
``````
0
Question by:Jeff S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 70

Expert Comment

ID: 20299763
A date of '' is 1900-01-01, since that is the default date in SQL Server.
0

LVL 75

Accepted Solution

Aneesh Retnakaran earned 1000 total points
ID: 20299789
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

LVL 70

Assisted Solution

Scott Pletcher earned 1000 total points
ID: 20299792
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
``````
0

LVL 75

Expert Comment

ID: 20299801
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

LVL 27

Expert Comment

ID: 20299805
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

LVL 7

Author Comment

ID: 20299853
Thanks a million ..... just the result I needed and awesome explanations! Thanks to all who helped.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month4 days, 16 hours left to enroll