convert DATETIME to Month & Year

[Jennifer24]

Hi.. can you please supply a SQL statement which will convert a datetime value to a Month Year format?

2001-07-17 14:32:20.763 ---> July 2001

thankyou !
Jen

 

[tlbroadbent]

Select convert(char(4),getdate(),100) + convert(char(4),getdate(),102) As MonYYYY Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Cagliostro]

use DatePart(m,yourdata) John Fill

ivfmd@mail.md

 

[tlbroadbent]

Here is one way to obtain the full month name in SQL 2000. Create a User Defined Function (UDF) like the following.

CREATE FUNCTION fn_GetMonthYear (@date datetime)
RETURNS varchar(14)
AS
BEGIN

Declare @mmmyyyy varchar(14)
Set @mmmyyyy=
Case month(@date)
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 + ' ' + str(year(@date),4)

RETURN(@mmmyyyy)

END

Execute the function. Assume col4 is the datetime column.

Select
col1, col2, col3,
mmmyyy=dbo.fn_getmonthyear(col4)
From tbl

-------------------------

In SQL 7, you don't have the luxury of a UDF so you can create a stored procedure or simply use the code in a query.

Select col1, col2, col3, mmmyyy=
Case month(col4)
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 + ' ' + str(year(col4),4)
From tbl
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[tlbroadbent]

I've just discovered an easier way to do get the full month name using the built in DATENAME function.

Select DATENAME(MONTH,GETDATE()) will return the full month name of the current month.

Select DATENAME(MONTH,DateCol) will return the full month name of the date in DateCol. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin