How to use CASE correctly on a date field

[snowneil]

Currently i have this as part of my statement:

c.closeDate is a 'smalldatetime' data type
The database sets a default null value in this field as 01/01/1900, i want to remove that value in a report.

CASE c.closeDate WHEN '01/01/1900' THEN '' ELSE c.closeDate END AS [closeDate],

The result displays the field closeDate but with no change.
The values that are set as 01/01/1900 still display as that, i am trying to clear them so it can be blank for a report in Excel.

Any help would be great.

 

[PHV]

You may try this:
COALESCE(c.closeDate, '') AS [closeDate],

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snowneil]

Thanks for the reply but that didnt do it.

I dont think the field is NULL, a default value is being put in of 01/01/1900.

The table is in a Microsoft SQL Server database.
When i do an insert to this table with no value for the close date it is automatically filled in with 01/01/1900.

 

[r937]

why not alter the table to allow null and then update the table to replace those values with null

r937.com | rudy.ca

 

[snowneil]

The insert is done via a .asp page.

Initially the value that gets inserted for close_date is "".

When viewing it in the dbase it is not "" or <NULL> but 01/01/1900.

I can change all the values of the current data but any new information will still go in as 01/01/1900.

I would rather not run an SQL UPDATE on my report page every time a report is requested if it can be helped.

 

[r937]

i suggest that you start a new thread in the sql server forum

there are many options here, but a lot will depend on what you really wish to accomplish and whether you are willing to ALTER the table if necessary

all of which is outside the scope of the ANSI SQL forum

r937.com | rudy.ca

 

[snowneil]

I have re-posted this thread under Microsoft SQL Server: Programming.

Hopefully that is the correct place.

Thanks

 

[Gunnien]

The database default NULL value is not 01/01/1900, but when one or another input-query sets the are setting the value to '' the database replaces this with 01/01/1900 to avoid an error that stops the work-flow.

And when you use this this statement in order to retrieve a blank field where the date is '01/01/1900':

CASE c.closeDate WHEN '01/01/1900' THEN '' ELSE c.closeDate END AS [closeDate],

You only replases the 01/01/1900 with the same date.

But if you changes your when statement to:

CASE c.closeDate WHEN '01/01/1900' THEN NULL ELSE c.closeDate END AS [closeDate],

You should get a blank field where the date is 01/01/1900.

Gunnien