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

Question

select DATENAME(month,29*5)

Can any one please tell me logic behind the above query.
How it always returns correct month name when provided month number as integer.

Answer 1

Datetime values in Sql server are stored on 8 bytes.
The first 4 bytes represents the date and the last 4 byte represents the time.

On the date part, date is stored as the number of days since 1900-01-01.

On the time part, it's the number of clock ticks since midnight.
There are 300 clock ticks per second, so a tick is 3.33333 milliseconds.
That's also the reason why datetime is only accurate to .003 of a second.

This query will hopefully help to explain:

SELECT  CAST(0 As datetime) As Date_0,
        29*5 As NumberOfDays, 
        CAST(29*5 as datetime) As TheDate,
        DATENAME(month,29*5) As TheMonthName

Results:

Date_0                      NumberOfDays    TheDate                     TheMonthName
-----------------------     ------------    -----------------------     ------------
1900-01-01 00:00:00.000     145             1900-05-26 00:00:00.000     May

As for the last part of your question, 29 (28 would work as well) is the magic number here - 30 is too big (May would be returned for 4 and 5) and 27 is too small - (September would be returned for 9 and 10).
Basically i'ts just math - get the number correctly so that each time you double it with any number between 1 and 12 will give you a number of days that sums up to a day that belongs to the correct month.

You can test it yourself using this script:

DECLARE @MagicNumber int = 28

;With cte as 
(
    select 1 as num
    union all
    select num + 1
    from cte 
    where num < 12
)

SELECT  num, DATENAME(month, @MagicNumber * num ) As TheMonthName
from cte

Just change the value of @MagicNumber and see the results you get.

Answer 2

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)

Answer 3

If you are simply want to show the month corresponding to month number then you should have to use like this.

  declare @intMonth as int
  set @intMonth = 5    
  Select DateName( month , DateAdd( month , @intMonth , -1 ))