sys.fn_MSdayasnumber
If you have realized the new function fn_MSdayasnumber in SQL Server 2005 you may have tried to find information about this system function fn_MSdayasnumber in the Books OnLine (BOL) the help document of the SQL Server. I could not find any documentation about the function sys.fn_MSdayasnumber on BOL. At least on my desktop installation the installed version of the BOL was not containing any information on sys.fn_MSdayasnumber
Searches on the web did not return any meaningful and helpful documentation.
So I called the fn_MSdayasnumber function to see what it does and what it returns.
Exec fn_MSdayasnumber
Exec sys.fn_MSdayasnumber
Both sql statements above returned the Msg 201 error message as shown below:
Msg 201, Level 16, State 4, Procedure fn_MSdayasnumber, Line 0
Procedure or Function 'fn_MSdayasnumber' expects parameter '@day', which was not
supplied.
I updated my query with a little bit complex one,
declare @day as datetime
set @day =Getdate()
declare @i as int
exec @i = fn_msdayasnumber @day
select @i
This sql query which I code for sys.fn_msdayasnumber returned the integer 2366 at the day when I'm writing this article.
Actually the function name is considering some explanation for the functionality of fn_msdayasnumber. Simply fn_msdayasnumber returns an integer for the date parameter passed as an argument beginning from some specific date.
I wondered if I can see the exact coding for this system function fn_msdayasnumber.
I opened the Microsoft SQL Server Management Studio and connected to a MS SQL Server 2005 database instance.
Then opened the new query window and typed the below command.
sp_helptext fn_MSdayasnumber
The returned query result will be as follows at least for the SQL Server 2005 SP1 version - 9.00.2047.00 (run select @@version to see your sql server installation version).
create function sys.fn_MSdayasnumber (@day datetime)
returns int
as
begin
return (year(@day) - 2000) * 366 + datepart(dayofyear,@day)
end
What is interesting with this function is that it returns days as integer from beginning the year 2000. And uses 366 as a constanst multiplier. This was interesting because after making a few test by calling the function fn_MSdayasnumber for some specific dates, I get some results that are incorrect.
We can repeat the result together.
declare @day as datetime
declare @i as int
set @day = '20000101'
exec @i = fn_msdayasnumber @day
select @i
go
Returns 1, so easy to understand '20000101' is the first day when we begin the date from year 2000
declare @day as datetime
declare @i as int
set @day = '20001231'
exec @i = fn_msdayasnumber @day
select @i
go
Returns 366 since the year 2000 is a leap year.
declare @day as datetime
declare @i as int
set @day = '20010101'
exec @i = fn_msdayasnumber @day
select @i
go
Returns 367, so one day later than the 366th day.
declare @day as datetime
declare @i as int
set @day = '20011231'
exec @i = fn_msdayasnumber @day
select @i
go
Returns 731, easy to understand that sum of 366 for year 2000 which is a leap year and 365 for 2001 equals to 731.
Now the interesting thing happens which surprised me though.
declare @day as datetime
declare @i as int
set @day = '20020101'
exec @i = fn_msdayasnumber @day
select @i
go
Returns 733 !!! I was waiting 732 to return back from the function call.
This is because 366 is used as a constant within the function declaration.
I prefer to use an updated version of this sql function instead of the original function fn_MSdayasnumber.
Let's call this function fn_MSdayasnumber2 and create this new function fn_MSdayasnumber2 by using the datadiff sql function and taking a base date which is the zero date in MS SQL Server.
create function fn_MSdayasnumber2 (@day datetime)
returns int
as
begin
return datediff(d, 0, @day) - datediff(d, 0, '20000101') + 1
end
Now you can run the below statements and see the return results and how they differ
declare @day as datetime
declare @i as int
set @day = '20020101'
exec @i = fn_msdayasnumber @day
select @i
exec @i = fn_msdayasnumber2 @day
select @i
go