Silly Date Formatting Question

[AccessUser22]

Hello,

I have a field in a query that I want to either show a dashed value '---' or a date. So I use this:

CASE WHEN [RegStatus] = 4 THEN '---' ELSE [Step7Date] END AS Step7

Problem is, it tells me "Conversion of the char data type to date time has resulted in an out of range value". If I set it up like this:

CASE WHEN [RegStatus] = 4 THEN '---' ELSE CAST([Step7Date] as nvarchar) END AS Step7

the date gets formatted as Apr 20, 2002 12:00:00 AM. What do I need to do if I want the date to be formatted as '4/20/02'?

 

[AlexCuse]

This should do it (not tested)

CASE WHEN [RegStatus] = 4 THEN '---' ELSE convert(varchar(10), [Step7Date], [b]101[/b]) END AS Step7

If SQL Server encounters two data types in the same expression (without any casting for compatibility) it follows what is called 'Data Type Precedence'. So as you found, you need to convert your Step7Date to varchar. Format 101 will give you the display format you need.

Here is some info on data type precedence:

http://msdn2.microsoft.com/en-us/library/aa258264(SQL.80).aspx

Hope it helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[jbenson001]

You could try convert(varchar(10),step7Date,101)

Or .. do it in the front end.

 

[AccessUser22]

Thanks! That worked perfectly.

 

[bborissov]

CASE WHEN [RegStatus] = 4
     THEN '---'
     ELSE CONVERT(varchar(10), [Step7Date], 101)
     END AS Step7

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP