Datename(Month,Getadate()) Is Returning Numeric Value of the Month as '09'

Convert Month Number to Month Name Function in SQL

A little hacky but should work:

SELECT DATENAME(month, DATEADD(month, @mydate-1, CAST('2008-01-01' AS datetime)))

How can I get the month number (not month name) from a date in SQL Server?

Use datepart function with m extension.

SELECT DATEPART(m, getdate())

Get 2 Digit Number For The Month

there are different ways of doing it

• Using RTRIM and specifing the range:

like

SELECT RIGHT('0' + RTRIM(MONTH('12-31-2012')), 2); 

• Using Substring to just extract the month part after converting the date into text

like

SELECT SUBSTRING(CONVERT(nvarchar(6),getdate(), 112),5,2)

see Fiddle

There may be other ways to get this.

Can any one please tell me logic behind select DATENAME(month,29*5)

I think I will able to explain.

The default year-month-day for any date data type is 1900-01-01. If we consider above select query, it add 29*5 days into default date and gives the MONTHNAME.

Select DATENAME(month,29*5)

Now understand the DATENAME

DateName - Returns a character string that represents the specified datepart of the specified date. Its have different -2 argument and give the different-2 result as per datepart.

Argument 1 - Is the part of the date to return.

Argument 2 - Is a any date (Is an expression that can be resolved to a
time, date, smalldatetime, datetime, datetime2, or datetimeoffset
value.)

Here we given month as a first argument. Which means it return monthname.

The calculation of 29*5 gives 145 answer and if we simply cast into date it consider as a days and calculate as 1900-01-01 + 145 and gives the date 1900-05-26 00:00:00.000.

Means if we get the month of this will give the 5 - MAY as a answer.

Execute this query and check the answer for the above logic.

Select DATENAME(month,29*5), (29*5)  , DATENAME(month, '12:10:30.123'), DATENAME(month, getdate()) 
select cast (145 as datetime)

DECLARE @t datetime = '12:10:30.123'; 
SELECT DATENAME(month, 29*5), 145/30.00; 

Check for further.
MSDN Link

Convert Month Number to Month Name Function in SQL (check the @user275683 answer)

Returning Month Name in SQL Server Query

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)

How to write an expression that adds text month to numerical value?

You can use a case expression:

SELECT CASE WHEN [Month] = 1 THEN '1-Jan'
WHEN [Month] = 2 THEN '2-Feb'
WHEN [Month] = 3 THEN '3-Mar'
...
END AS [MonthName]

Alternative is using DATENAME:

SELECT CAST(MONTH(1) AS NVARCHAR(2))+'-'+LEFT(UPPER(DATENAME(MONTH,MONTH(1))),3)

This has been asked before: Convert Month Number to Month Name Function in SQL

If you do not want a case expression.

Getting only Month and Year from SQL DATE

As well as the suggestions given already, there is one other possiblity I can infer from your question:

- You still want the result to be a date

- But you want to 'discard' the Days, Hours, etc

- Leaving a year/month only date field

SELECT
   DATEADD(MONTH, DATEDIFF(MONTH, 0, <dateField>), 0) AS [year_month_date_field]
FROM
   <your_table>

This gets the number of whole months from a base date (0) and then adds them to that base date. Thus rounding Down to the month in which the date is in.

NOTE: In SQL Server 2008, You will still have the TIME attached as 00:00:00.000
This is not exactly the same as "removing" any notation of day and time altogether.
Also the DAY set to the first. e.g. 2009-10-01 00:00:00.000

SQL Server : query to return month name instead of number

Instead of Month function use Datename function with Month datepart

select  datename(month,date) [month]
       ,isnull(sum(case when year(DATE) = 2015 then sales end), 0) as '2015'
from tblSales
where tenantcode = 'cmbina13'
group by  datename(month,date)
Order by DATEPART(MM,datename(month,date)+' 01 2011')

Get week day name from a given month, day and year individually in SQL Server

You need to construct a date string. You're using / or - operators which do MATH/numeric operations on the numeric return values of DATEPART. Then DATENAME is taking that numeric value and interpreting it as a date.

You need to convert it to a string. For example:

SELECT (
  DATENAME(dw, 
  CAST(DATEPART(m, GETDATE()) AS VARCHAR) 
  + '/' 
  + CAST(DATEPART(d, myDateCol1) AS VARCHAR) 
  + '/' 
  + CAST(DATEPART(yy, getdate()) AS VARCHAR))
  )

---

Related Topics