Returning Month Name in SQL Server Query
SqlSql Server-2008Type ConversionSql Problem Overview
Using SQL Server 2008, I have a query that is used to create a view and I'm trying to display a month's name instead of an integer.
In my database, the datetime is in a column called OrderDateTime. The lines in the query that return the date is:
DATENAME(yyyy, S0.OrderDateTime) AS OrderYear,
DATEPART(MONTH, S0.OrderDateTime) AS OrderMonth
This returns a column of years and a column of months as integers. I want to return the month names (Jan, Feb, etc). I've tried:
CONVERT(varchar(3), DATEPART(MONTH, S0.OrderDateTime) AS OrderMonth
This is obviously is incorrect, as I get
> Incorrect syntax near 'AS'
message. What is the proper syntax for my query?
Sql Solutions
Solution 1 - Sql
This will give you the full name of the month.
select datename(month, S0.OrderDateTime)
If you only want the first three letters you can use this
select convert(char(3), S0.OrderDateTime, 0)
Solution 2 - Sql
Have you tried DATENAME(MONTH, S0.OrderDateTime) ?
Solution 3 - Sql
Change:
CONVERT(varchar(3), DATEPART(MONTH, S0.OrderDateTime) AS OrderMonth
To:
CONVERT(varchar(3), DATENAME(MONTH, S0.OrderDateTime)) AS OrderMonth
Solution 4 - Sql
Try this:
SELECT LEFT(DATENAME(MONTH,Getdate()),3)
Solution 5 - Sql
DECLARE @iMonth INT=12
SELECT CHOOSE(@iMonth,'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER')
Solution 6 - Sql
Select SUBSTRING (convert(varchar,S0.OrderDateTime,100),1,3) from your Table Name
Solution 7 - Sql
In SQL Server 2012 it is possible to use FORMAT(@mydate, 'MMMM') AS MonthName
Solution 8 - Sql
This will give you what u are requesting for:
select convert(varchar(3),datename(month, S0.OrderDateTime))
Solution 9 - Sql
SELECT MONTHNAME( `col1` ) FROM `table_name`
Solution 10 - Sql
for me DATENAME was not accessable due to company restrictions.... but this worked very easy too.
FORMAT(date, 'MMMM') AS month
Solution 11 - Sql
Without hitting db we can fetch all months name.
WITH CTE_Sample1 AS
(
Select 0 as MonthNumber
UNION ALL
select MonthNumber+1 FROM CTE_Sample1
WHERE MonthNumber+1<12
)
Select DateName( month , DateAdd( month , MonthNumber ,0 ) ) from CTE_Sample1
Solution 12 - Sql
basically this ...
declare @currentdate datetime = getdate()
select left(datename(month,DATEADD(MONTH, -1, GETDATE())),3)
union all
select left(datename(month,(DATEADD(MONTH, -2, GETDATE()))),3)
union all
select left(datename(month,(DATEADD(MONTH, -3, GETDATE()))),3)