Cast date to string, want MM/DD/YYYY formatting

[TheBrenda]

Re: Cast date to string, want MM/DD/YYYY formatting

Have a column where the DOB was cast to a varchar. Want to hide certain birthdays so XX/XX/XXXX shows under certain circumstances. When the cast to varchar was done it formatted the date as 1994-02-03. I want the date formatted as MM/DD/YYYY. Is there a FORMAT statement or such that can be done on the dates, or do I perform substrings to move the date around?

[Lynn]

I'd make the same suggestion I did for your other thread and use a conditional style rather than changing the actual data type of the query item.

[TheBrenda]

Yes, Lynn's suggestion on my other related thread worked like a charm. It was "how to format a string as currency"

http://www.cognoise.com/index.php/topic,26520.0.html

[anjuleeca]

Please try
rtrim(substring(date,6,2)+'/'+substring(date,1,2)+'/'+substring(date,1,4))

[dougp]

You can't substring a date.

Try these:
select cast(month(getdate()) as varchar(2)) + '/' + cast(day(getdate()) as varchar(2)) + '/' + cast(year(getdate()) as varchar(4))  -- m/d/yyyy
select right('00' + cast(month(getdate()) as varchar(2)), 2) + '/' + right('00' + cast(day(getdate()) as varchar(2)), 2) + '/' + right('0000' + cast(year(getdate()) as varchar(4)), 4)  -- mm/dd/yyyy
select convert(varchar(10), getdate(), 101)  -- mm/dd/yyyy

Replace the getdate() function with whatever your date expression is.