Date question

I currently get a date in the default format in my stored procedure. Up until now converting the date to type 101 was acceptable. Now they want the date to read something like Wednesday, November 8, 2006 or November 8, 2006. How do I do this convert in a single select statement?

I found the types that add the time but I have to make sure that the time isn't included for merge reasons.

Help

Jeff

Mookey:

Are you looking for something like this:

declare @.myDate datetime set @.myDate = getdate()

select dateName (dw, @.myDate) + ', ' +
dateName (month, @.myDate) + ' ' +
convert (varchar (2), datepart (dd, @.myDate)) + ', ' +
convert (varchar (4), year(@.myDate)) as [Date Name String]

--
-- S A M P L E O U T P U T :
--

-- Date Name String
-- -
-- Wednesday, November 8, 2006

|||Formatting of dates should be preferable done in the frontent not in the backend.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Mookey wrote:

I currently get a date in the default format in my stored procedure. Up until now converting the date to type 101 was acceptable. Now they want the date to read something like Wednesday, November 8, 2006 or November 8, 2006. How do I do this convert in a single select statement?

I found the types that add the time but I have to make sure that the time isn't included for merge reasons.

Help

Jeff

SELECT CAST(DATENAME(MONTH, GETDATE()) AS varchar(15)) + ' ' + CAST(DATEPART(DAY, GETDATE()) AS varchar(2)) + ', ' + CAST(DATEPART(YEAR, GETDATE()) AS varchar(4))

Adamus

|||

I second what Jens states (unless you are doing a one time data conversion using SQL, then follow the other ideas :).

Leave the formatting of dates (and any values) to the presentation layer. There should be some common object that they reuse for all date values that are returned in the native SQL Server date datatype, which is not actually formatted any way. SSMS uses a standard format to display dates, which is the best format for passing dates, but clearly not what the average person want to see in the UI. Then the formatting of dates can be tied to the machines formatting, or controlled via a configuration that all users use.