blanking a date

[rds80]

CASE WHEN CM.ApprovedDate IS NULL THEN ''
                                              ELSE CM.ApprovedDate END

What I would like to do is if ApprovedDate IS NULL then blank the cell, otherwise populate the cell.

however, the results from the query is that if the approvedDate is NULL then it outputs 1900-01-01 00:00:00.000.

Any suggestions?

 

[SQLDenis]

convert to varchar first , you don't need case either you can use COALESCE instead

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ESquared]

You're trying to do with the database what is a presentation issue. For what it's worth, it's probably better to do presentation in the presentation layer.

It also sounds like you might benefit from a tutorial on SQL Server datetime values. There's one in the FAQ section for this forum.

 

[SQLBill]

There are two things you could be trying to do and we need to know which of them is the one you mean.

1. You want to display the value as a blank (presentation)
or
2. You want to enter a blank into the database (storage).

For displaying, SQLDenis and ESquared point you in the correct direction.

If you want to STORE a blank, that's different. Since your column is DATETIME, SQL Server will use the default DATETIME value which is 1900-01-01 00:00:00.000.

I'm not sure you can change that by making the column NULLable.

-SQLBill

Posting advice: FAQ481-4875

 

[rds80]

i'll be copying the results from the sql query to excel.
will take a look at the FAQ. thanks.

 

[rds80]

From the books online, I read this about COALESCE: Returns the first nonnull expression among its arguments. If all arguments are NULL, COALESCE returns NULL.

But if the value is NULL I want a blank '' to appear so when I copy the data from the query to Excel, I dont have to replace the NULLs with a ''.

 

[gmmastros]

This...

[tt]
CASE WHEN CM.ApprovedDate IS NULL
THEN ''
ELSE CM.ApprovedDate END
[/tt]
Can be written as:

[tt][blue]
Coalesce(CM.ApprovedDate, '')
[/blue][/tt]

so, if ApprovedDate is NULL, then an empty string will be returned, otherwise you will get the value of ApprovedDate.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds80]

Tried that and where ApprovedDate is NULL it returns 1900-01-01.

 

[gmmastros]

Try...

Coalesce(Convert(VarChar(20), CM.ApprovedDate, [!]101[/!]), '')

Lookup the Convert function in books on line. 101 is a special type of formatting for the date. There are others, and books on line will show you what the other formatting options are.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Are you CONVERTING or CASTING your DATETIME value to CHAR or VARCHAR as everyone has suggested?

-SQLBill

Posting advice: FAQ481-4875

 

[rds80]

That did the work, thanks..

how would I round this w/ 2 decimal values

CASE WHEN ABS(EP.OwnedByLender) = '1' THEN SUM(EP.PercentOfNote)
                                       ELSE MIN(EP.PercentofNote) END * N.OrigLoanAmount

I tried Round((CASE WHEN ABS(EP.OwnedByLender) = '1' THEN SUM(EP.PercentOfNote)
ELSE MIN(EP.PercentofNote) END * N.OrigLoanAmount, 2)

But that still gives me values beyond the 2 decimal values.