How to get date in format: Thurday, March 25

[Mario2002]

Hi,

Does anyone know how I can get a date in my SQL Server to return a date in the following format:
Thursday, March 25 2002. I am using SQLServer 7.0. Is there a function to return this format. I have searched in the Online Books but I have not found any.

Thanks

Mario

 

[sunila7]

Hi,

Try this

Select
CASE datepart(dw,dtcol)
When 1 then 'Sunday,'
When 2 then 'Monday,'
When 3 then 'Tuesday,'
When 4 then 'Wednesday,'
When 5 then 'Thursday,'
When 6 then 'Friday,'
Else 'Saturday,'
End
+
Convert(char(12), getdate(),107)
From TBLName

Sunil

 

[edgardg]

try this:
LEFT(CAST(dField AS CHAR), 11)

 

[SemperFiDownUnda]

as the 2 examples show there isn't a way to put in a mask and get a string in a given format.
SQL server's DATETIME to STRING conversions are fairly limited and sunila7's solution is going to be the closest to what you are asking for though you could get a full blown version of what you want by expanding it to the following

Select
CASE DATEPART(DW,GETDATE())
When 1 then 'Sunday,'
When 2 then 'Monday,'
When 3 then 'Tuesday,'
When 4 then 'Wednesday,'
When 5 then 'Thursday,'
When 6 then 'Friday,'
Else 'Saturday,'
End
+ ' ' +
CASE DATEPART(MONTH,GETDATE())
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
ELSE 'December'
End
+ ' ' +
CONVERT(CHAR(2), DATEPART(DAY,GETDATE()))
+ ' ' + CONVERT(CHAR(4), DATEPART(yyyy,GETDATE()))

of course replace GETDATE() with your datetime variable

 

[tlbroadbent]

Here are a couple of alternatives.

Select
datename(dw, getdate()) + ', ' +
convert(varchar(11), getdate(), 100)

Select
datename(dw, getdate()) + ', ' +
datename(month,getdate()) + ' ' +
str(day(getdate()),2) + ' ' +
str(year(getdate()),4) If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm