Returning Month Name in SQL Server Query
425,705
Solution 1
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
Have you tried DATENAME(MONTH, S0.OrderDateTime)
?
Solution 3
Change:
CONVERT(varchar(3), DATEPART(MONTH, S0.OrderDateTime) AS OrderMonth
To:
CONVERT(varchar(3), DATENAME(MONTH, S0.OrderDateTime)) AS OrderMonth
Solution 4
Try this:
SELECT LEFT(DATENAME(MONTH,Getdate()),3)
Solution 5
DECLARE @iMonth INT=12
SELECT CHOOSE(@iMonth,'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER')
Author by
Admin
Updated on December 19, 2020Comments
-
Admin over 3 years
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 calledOrderDateTime
. 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?
-
Druid over 11 yearsI think you mean
DATENAME
.DATEPART
wouldn't do it. -
Petter Friberg over 7 yearsComment incorrectly posted in answer "What is S0 mean in the sql query?" by user7157710
-
Bpainter almost 7 yearsS0 is an alias for a table. The answer is carrying it over from the question.
-
RobIII over 5 yearsI'd like to add that, typically, you don't even want to use these functions. You're better off selecting the date as date(time) or month as integer or whatever and then let your UI take care of 'converting' it to a human-friendly month name. That way your application / UI can take care of localization rather than your database, which doesn't have the slightest clue on your user's preferences or application's settings for localization. To put it in another way, think about it like this: what if your user prefers the Chinese 二月 over february?