Display date format???

RinoDM · May 6th, 2009, 10:47 AM

Hi to all...

In a select statement is there a way i can display the date format as

Wednesday May 6, 2009

Thanking you in advance

Rino

rstelma · May 6th, 2009, 11:47 AM

Hi,

You can use this User Defined Function to get the results you desire.

CREATE FUNCTION getLongDate(@InputDate Datetime)
RETURNS Varchar(30)
AS
BEGIN

RETURN

CASE WHEN DATEPART(dw,@InputDate) = 1 Then 'Sunday' WHEN DATEPART(dw,@InputDate) = 2 Then 'Monday'
WHEN DATEPART(dw,@InputDate) = 3 Then 'Tuesday'
WHEN DATEPART(dw,@InputDate) = 4 Then 'Wednesday'
WHEN DATEPART(dw,@InputDate) = 5 Then 'Thursday'
WHEN DATEPART(dw,@InputDate) = 6 Then 'Friday'
WHEN DATEPART(dw,@InputDate) = 7 Then 'Saturday'
END + ' ' +
CASE
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 1 Then 'January'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 2 Then 'February'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 3 Then 'March'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 4 Then 'April'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 5 Then 'May'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 6 Then 'June'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 7 Then 'July'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 8 Then 'August'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 9 Then 'September'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 10 Then 'October'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 11 Then 'November'
WHEN CONVERT(Varchar, DATEPART(mm, @InputDate)) = 12 Then 'December'
END + ' ' +
CONVERT(Varchar,DATEPART(dd, @InputDate), 0) + ', ' + CONVERT(Char(4), DATEPART(yy, @InputDate))

END

SELECT dbo.getLongDate([Date Column]) AS TheLongDate FROM [Some Table]

RinoDM · May 6th, 2009, 03:07 PM

I can add this to my store procedure where i do my select statement right ???

Jeff Moden · May 6th, 2009, 05:01 PM

Quote:

Originally Posted by RinoDM

I can add this to my store procedure where i do my select statement right ???

Yeah... but it doesn't need to be nearly that complicated....

Code:

 SELECT DATENAME(dw,GETDATE())+' '
      + DATENAME(mm,GETDATE())+' '
      + DATENAME(dd,GETDATE())+', '
      + DATENAME(yy,GETDATE())

RinoDM · May 6th, 2009, 05:10 PM

works great...
thanks

Jeff Moden · May 6th, 2009, 05:29 PM

Thanks for the feedback, Rino.

rstelma · May 6th, 2009, 05:47 PM

Jeff,

Much better. Was not familiar with DATENAME.

Also, I figured there had to be a much easier way.

Thanks,
Richard

Old Pedant · May 6th, 2009, 06:42 PM

For future reference...

Even if you were forced into a CASE...WHEN, the form used by RSTELMA is not the optimum choice.

Much better than

Code:

CASE WHEN DATEPART(dw,@InputDate) = 1 Then 'Sunday' WHEN DATEPART(dw,@InputDate) = 2 Then 'Monday'
 WHEN DATEPART(dw,@InputDate) = 3 Then 'Tuesday'
 WHEN DATEPART(dw,@InputDate) = 4 Then 'Wednesday'
 WHEN DATEPART(dw,@InputDate) = 5 Then 'Thursday'
 WHEN DATEPART(dw,@InputDate) = 6 Then 'Friday'
 WHEN DATEPART(dw,@InputDate) = 7 Then 'Saturday'
END

would be this:

Code:

CASE DATEPART(dw,@InputDate) 
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
END

Do *NOT* repeat the expression when it doesn't change throughout the CASE. Use this alternate form, instead. It will look pretty familiar to anybody who has used VB or even switch( ) in many other languages.

Jeff Moden · May 6th, 2009, 07:00 PM

Quote:

Originally Posted by rstelma

Jeff,

Much better. Was not familiar with DATENAME.

Also, I figured there had to be a much easier way.

Thanks,
Richard

Absolutely my pleasure, Richard. Thank you for the feedback.

Jeff Moden · May 6th, 2009, 07:05 PM

Quote:

Originally Posted by Old Pedant

For future reference...

Even if you were forced into a CASE...WHEN, the form used by RSTELMA is not the optimum choice.

I agree although I've never tested it for actual performance. It would be interesting to test on a million row table.