How to use CASE correctly on a date field 1

[snowneil]

Currently i have this as part of my statement:

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

Microsoft SQL Server database.

c.closeDate is a 'smalldatetime' data type.

I want to show the 01/01/1900 values as "" so they dont show in an Excel report.

The above code doesn't change the recordset and still displays the closeDate.

The insert is done via a .asp page.

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

When viewing it in the dbase it is not ""(Blank) 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.

Any help would be great.

 

[gmmastros]

You are mixing data types. When the date is 1/1/1900, you return a string, otherwise, you return a small date time. Try converting the small date time to a string instead.

CASE c.closeDate WHEN '01/01/1900' THEN '' ELSE [!]Convert(VarChar(10),[/!] c.closeDate[!])[/!] END AS [closeDate],

-George

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

 

[r937]

why can't you store actual NULLs instead of the 1/1/1900 date?

r937.com | rudy.ca

 

[snowneil]

I don't know.

I can't see an option for it, it automatically puts in 01/01/1900 into the database when i enter a new record.

 

[vongrunt]

Probably column DEFAULT... blargh

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[jbenson001]

If you send a blank '' to the table then it will store 01/01/1900 by default. You have to find a way from asp to send an actual NULL value. If you are using .NET, you can use System.Convert.DBNULL, I am not sure about classic ASP.

Jim

 

[Gunnien]

Database default for '' is '01/01/1900' so the only you achieve with this statement:

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

is to replace '01/01/1900' with '01/01/1900'.

Try this one:

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

This shoul work fine.

Gunnien

 

[snowneil]

Yep that worked, thanks for that.