Title

Kodyaz Development Resources

Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.
Welcome to Kodyaz Development Resources Sign in | Join | Help

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
 



Copyright © 2004 - 2008 Eralper Yilmaz. All rights reserved.
Powered by Community Server, by Telligent Systems