Converting to long date in MSSQL

[Nuovo2004]

Hi All,
This is probably a simple one but I couldn't find what I was looking for in the posts.
I'm making a report using MSSQL reporting services and I'd like to insert a field displaying the current date in a specific format (e.g. August 2, 2004)
I followed some threads here and came up with the following:
SELECT CONVERT(varchar, getdate(), 107) as CurrentDate.
In this case, I get CurrentDate = Jun 25, 2004. Format 107 was the closest thing I could find in books online.
I'd like the month to be full text 'June' and not abbreviated 'Jun'.
Suggestions?
Thanks.

 

[Nuovo2004]

I devised an ugly manual way to do it but I'm hoping something better will come along:
/*---------------------------------------*/
Declare @Curmonth as varchar(10)
Declare @CurDay as varchar(2)
Declare @CurYear as varchar(4)
Set CurMonth = datename(month,getdate())
Set CurYear = datename(year(getdate())
Set CurDay = datename(day(getdate())
Select @CurMonth + space(1) + @CurDay + ',' + space(1) + @CurYear
/*---------------------------------------*/
Output is 'June 25, 2004' but I am concerned that this date format is only correct due to my system settings. I'd like to explicitely dictate that month is alpha, day is dd and year is yyyy.

 

[JayKusch]

You could also bypass the DECLAREs and SETs and just go for:

Select datename(month,getdate())
     + ' ' 
     + datename(day,getdate())
     + ', ' 
     + datename(year,getdate())

Thanks

J. Kusch

 

[BobJacksonNCI]

Disclaimer: I work more in Access than in SQL, for now.

You might try:

Select DateName(month,getdate()) + ' ' + DatePart(day,getdate()) + ', ' + DatePart(year,getdate()) as CurrentDate



HTH,
Bob

 

[Nuovo2004]

Much cleaner guys, thanks.
One last thing, will this be a standard date format across all machines regardless of date and time preferences?

 

[Nuovo2004]

Actually JayJusch's version was successful but Bob's wasn't.
When I execute the query using the DateName and DatePart functions in the same command, it get this error message:
"Server: Msg 245, Level 16, State 1, Line 13
Syntax error converting the nvarchar value 'June ' to a column of data type int."
If I run the statement using only DateName functions I get the correct string.
Perhaps I need to convert the output of datepart(day(getdate()) to varchar before adding it to the string?

 

[JayKusch]

Try ...

Select (datename(month,getdate())
      + ' ' 
      + datename(day,getdate())
      + ', ' 
      + datename(year,getdate()) ) as 'CurrentDate'

Thanks

J. Kusch

 

[BobJacksonNCI]

Since Jay's answer works...

SQL doesn't like mixing the string output of DateName with the integer output of DatePart. I thought DateName(Day... would return Friday instead of 25. Good deal - I'm smarter than I was a few minutes ago!

Bob

 

[Nuovo2004]

Both of your queries work Jay.
Thank you.

MSSQL reporting services is an interesting tool. As I familiarize myslef with it, I'll be sure to add any insight I gain to this site.

 

[ESquared]

Just so you know, if you're using Access and the query is being performed by the Access engine, then you can do

Format(Date,"mmm dd yyyy")