360-Day Calendar Year Function...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

360-Day Calendar Year Function...

Author

Topic

SouthSideRob
Starting Member

22 Posts

Posted - 2008-06-23 : 11:49:17

I'm looking for a function that will return an INTEGER that computes the number of days between two dates based on a 360-calendar year instead of using just the datediff function which is actual days.

If anybody knows where I could find something like this, I would be greatly appreciated.

Does this site have a library of user-defined sql functions? Or, does somebody have a site that you know of where there are a bunch?

Thanks,

South Side Rob

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-23 : 11:53:36

I think you need to give some more explanation about how a 360 day calendar year works and what you are trying to do.

Does this mean that each year is 360 days long and you just start a new year on day 361?

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 12:05:35

Microsoft Excel has this explanation

quote:
DAYS360

See Also

Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

Syntax

DAYS360(start_date,end_date,method)

Start_date and end_date are the two dates between which you want to know the number of days. If start_date occurs after end_date, DAYS360 returns a negative number. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Method is a logical value that specifies whether to use the U.S. or European method in the calculation.

Method Defined
FALSE or omitted U.S. (NASD) method. If the starting date is the 31st of a month, it becomes equal to the 30th of the same month. If the ending date is the 31st of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise the ending date becomes equal to the 30th of the same month.
TRUE European method. Starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month.

Remark
Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

E 12°55'05.25"
N 56°04'39.16"

SouthSideRob
Starting Member

22 Posts

Posted - 2008-06-23 : 12:07:43

I work for a bank. All of our accounts calculate days past due on a 360 day calendar year. In excel, there is a function called DAYS360 but I need to know the formula as all my data is housed in SQL 2005. As an example, two dates, 2/1/2008 and 6/1/2008. Using datediff, it returns 121 days. But if you use the days360 function in excel, it returns 120. A 360-day calendar year treats every month as a 30-day month. I'm looking for the actual formula in a sql script that will allow me to send it two dates and return an integer as the result.

SouthSideRob
Starting Member

22 Posts

Posted - 2008-06-23 : 12:13:42

Here is a function that I created (I might have found it somewhere on the net, I can't remember). Anyway, it appears to be one day off so I was looking for another function to compare the two.


CREATE FUNCTION dbo.fnGetDays360(@start_dt AS DATETIME, @end_dt AS DATETIME)
RETURNS INT
AS
BEGIN

/************************************************************
*                                                           *
*    This function is used to return the days past due      *
*    bucket based on a 360-day year.                        *
*                                                           * 
*    Created By: Rob Bruesewitz                             *
*    Created On: April 15th, 2008                           *
*                                                           *
************************************************************/

/* 
1) Determine the last day of this month
2) Adjust the Start Date
3) Adjust the End Date
*/

  DECLARE @last_day_of_month AS DATETIME
  DECLARE @sd AS DATETIME
  DECLARE @ed AS DATETIME

  --Check for Start Date of 1/1/1901
  IF @start_dt < '1/2/1901'
    BEGIN
        RETURN 0
    END

  --Calculate Last Day of the Month
  SET @last_day_of_month = 
  CASE
    WHEN MONTH(@end_dt) IN (2) AND YEAR(@end_dt) IN (2008, 2012, 2016, 2020, 2024, 2028) THEN
      CAST(MONTH(@end_dt) AS VARCHAR) + '/29/' + CAST(YEAR(@end_dt) AS VARCHAR)
    WHEN MONTH(@end_dt) IN (2) THEN
      CAST(MONTH(@end_dt) AS VARCHAR) + '/28/' + CAST(YEAR(@end_dt) AS VARCHAR)
    WHEN MONTH(@end_dt) IN (4,6,9,11) THEN
      CAST(MONTH(@end_dt) AS VARCHAR) + '/30/' + CAST(YEAR(@end_dt) AS VARCHAR)
    ELSE
      CAST(MONTH(@end_dt) AS VARCHAR) + '/31/' + CAST(YEAR(@end_dt) AS VARCHAR)
  END

  --Calculate Start Date
  IF DAY(@start_dt) > 30
    BEGIN
      SET @sd = CAST(MONTH(@start_dt) AS VARCHAR) + '/30/' + CAST(YEAR(@start_dt) AS VARCHAR)
    END

  ELSE
    BEGIN
      SET @sd = @start_dt
    END

  --Calculate End Date
  IF @end_dt = @last_day_of_month
    BEGIN
      IF DAY(@start_dt) < 30
        BEGIN
          SET @ed = DATEADD(d, 1, @end_dt)
        END
  
      ELSE
        BEGIN
          SET @ed = 
          CASE
          WHEN MONTH(@end_dt) IN (2) AND YEAR(@end_dt) IN (2008, 2012, 2016, 2020, 2024, 2028) THEN
            CAST(MONTH(@end_dt) AS VARCHAR) + '/29/' + CAST(YEAR(@end_dt) AS VARCHAR)
          WHEN MONTH(@end_dt) IN (2) THEN
            CAST(MONTH(@end_dt) AS VARCHAR) + '/28/' + CAST(YEAR(@end_dt) AS VARCHAR)
          ELSE
            CAST(MONTH(@end_dt) AS VARCHAR) + '/30/' + CAST(YEAR(@end_dt) AS VARCHAR)
          END
        END
    END

    ELSE
      BEGIN
        SET @ed = @end_dt
      END

  RETURN (DATEDIFF(m, @sd, @ed) * 30) + (DAY(@ed) - DAY(@sd))

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 12:17:48

Copied from Excel

SET NOCOUNT ON

DECLARE	@Sample TABLE
	(
		source DATETIME,
		target datetime default '2010-09-06',
		days360 INT
	)

INSERT @Sample (source, days360) VALUES (39083, 1325)

SELECT	*
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

SouthSideRob
Starting Member

22 Posts

Posted - 2008-06-23 : 12:36:43

Peso,

Thanks for the help but I'd have to flush and fill that table every day, then, inner join the dates. Not saying I can't do that but I would think a function close to what I have would work just as well. Do you know where there is a bank of user-defined functions publicly available for sql functions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 12:44:28

The sample data is just for verification.

E 12°55'05.25"
N 56°04'39.16"

SouthSideRob
Starting Member

22 Posts

Posted - 2008-06-23 : 12:45:47

Peso,

Do you know where I can find user-defined functions in sql created by others that I can browse through???

SouthSideRob
Starting Member

22 Posts

Posted - 2008-06-23 : 13:53:41

I figured it out with a colleague of mine. I will print the sql function on a reply shortly so that its on the site. Thanks Peso for helping out. I do appreciate it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 14:07:19

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105375

E 12°55'05.25"
N 56°04'39.16"

SouthSideRob
Starting Member

22 Posts

Posted - 2008-06-23 : 14:25:10

Here is what we did. Because we do not reboot our sql-server at work very often, we check the sysobjects so that we can drop and re-create rather than alter.


IF EXISTS(
		SELECT 'X'
		FROM sysobjects WHERE NAME = 'fnGetDays360'
	 )
BEGIN
	DROP FUNCTION fnGetDays360
END

GO
CREATE FUNCTION dbo.fnGetDays360(@start_dt AS DATETIME, @end_dt AS DATETIME)
RETURNS INT
AS
BEGIN

/************************************************************
*                                                           *
*    This function is used to return the days past due      *
*    bucket based on a 360-day year.                        *
*                                                           * 
*    Created By: Rob                                        *
*    Created On: June 23rd, 2008                            *
*                                                           *
************************************************************/

  DECLARE @ldom1 AS DATETIME
  DECLARE @ldom2 AS DATETIME

  --Check for Start Date of 1/1/1901
  IF @start_dt < '1/2/1901'
    BEGIN
        RETURN 0
    END

  --Return the Last Days of the Month for Each Date
  SET @ldom1 = (SELECT MIS_Dept.dbo.fnGetLastDayOfMonth(@start_dt))
  SET @ldom2 = (SELECT MIS_Dept.dbo.fnGetLastDayOfMonth(@end_dt))

/************************************************************
  Rule 1 
  If Both Dates fall on the last day of February then
  add 1 or 2 days to @end_dt depending on how many days are
  in the month of February that year.
************************************************************/
  IF (@start_dt = @ldom1) AND (@end_dt = @ldom2)
  BEGIN
    IF DAY(@end_dt) = 28
    BEGIN
      SET @end_dt = DATEADD(d, 2, @end_dt)
    END
    IF DAY(@end_dt) = 29
    BEGIN
      SET @end_dt = DATEADD(d, 1, @end_dt)
    END
  END

/************************************************************
  Rule 2
  If @start_dt falls on the 31st or a month of the last day
  of February, then change @start_dt to reflect the 30th of
  the same month. For February, you need to add 1 or 2 days
  depending on leap year. For 31 day months, you need to
  subtract a day.
************************************************************/
  IF (DAY(@start_dt) = 31) OR (MONTH(@start_dt) = 2 AND @start_dt = @ldom1)
  BEGIN
    IF DAY(@start_dt) = 28
    BEGIN
      SET @start_dt = DATEADD(d, 2, @start_dt)
    END
    IF DAY(@start_dt) = 29
    BEGIN
      SET @start_dt = DATEADD(d, 1, @start_dt)
    END
    IF DAY(@start_dt) = 31
    BEGIN
      SET @start_dt = DATEADD(d, -1, @start_dt)
    END
  END

/************************************************************
  Rule 3
  If @start_dt falls on the 30th of a month after adjusting
  for the first two rules and @end_dt falls on the 31st of a
  month, then, subtract one day from the @end_dt.
************************************************************/
  IF DAY(@start_dt) = 30 AND DAY(@end_dt) = 31
  BEGIN
    SET @end_dt = DATEADD(d, -1, @end_dt)
  END

  --Return the result
  RETURN (DATEDIFF(m, @start_dt, @end_dt) * 30) + (DAY(@end_dt) - DAY(@start_dt))

END

SouthSideRob
Starting Member

22 Posts

Posted - 2008-06-24 : 09:42:45

Here is the updated function. We found that in rule 2, you cannot add 1 or 2 days to the end of February without also added the same amount of days to the other date. Now, we've tested it and it works perfectly.


IF EXISTS(
		SELECT 'X'
		FROM sysobjects WHERE NAME = 'fnGetDays360'
	 )
BEGIN
	DROP FUNCTION fnGetDays360
END

GO
CREATE FUNCTION dbo.fnGetDays360(@start_dt AS DATETIME, @end_dt AS DATETIME)
RETURNS INT
AS
BEGIN

/************************************************************
*                                                           *
*    This function is used to return the days past due      *
*    bucket based on a 360-day year.                        *
*                                                           * 
*    Created By: Rob                                        *
*    Created On: June 24th, 2008                            *
*                                                           *
************************************************************/

  DECLARE @ldom1 AS DATETIME
  DECLARE @ldom2 AS DATETIME
  DECLARE @dpd AS INT

  --Check for Start Date of 1/1/1901
  IF @start_dt < '1/2/1901'
    BEGIN
        RETURN 0
    END

  --Return the Last Days of the Month for Each Date
  SET @ldom1 = (SELECT MIS_Dept.dbo.fnGetLastDayOfMonth(@start_dt))
  SET @ldom2 = (SELECT MIS_Dept.dbo.fnGetLastDayOfMonth(@end_dt))

/************************************************************
  Rule 1 
  If Both Dates fall on the last day of February then
  add 1 or 2 days to @end_dt depending on how many days are
  in the month of February that year.
************************************************************/
  IF (@start_dt = @ldom1) AND (@end_dt = @ldom2)
  BEGIN
    IF DAY(@end_dt) = 28
    BEGIN
      SET @end_dt = DATEADD(d, 2, @end_dt)
    END
    IF DAY(@end_dt) = 29
    BEGIN
      SET @end_dt = DATEADD(d, 1, @end_dt)
    END
  END

/************************************************************
  Rule 2
  If @start_dt falls on the 31st or a month of the last day
  of February, then change @start_dt to reflect the 30th of
  the same month. For February, you need to add 1 or 2 days
  depending on leap year (Move Both Dates). 
  For 31 day months, you need to subtract a day.
************************************************************/
  IF (DAY(@start_dt) = 31) OR (MONTH(@start_dt) = 2 AND @start_dt = @ldom1)
  BEGIN
    IF DAY(@start_dt) = 28
    BEGIN
      SET @start_dt = DATEADD(d, 2, @start_dt)
      SET @end_dt = DATEADD(d, 2, @end_dt)
    END
    IF DAY(@start_dt) = 29
    BEGIN
      SET @start_dt = DATEADD(d, 1, @start_dt)
      SET @end_dt = DATEADD(d, 1, @end_dt)
    END
    IF DAY(@start_dt) = 31
    BEGIN
      SET @start_dt = DATEADD(d, -1, @start_dt)
    END
  END

/************************************************************
  Rule 3
  If @start_dt falls on the 30th of a month after adjusting
  for the first two rules and @end_dt falls on the 31st of a
  month, then, subtract one day from the @end_dt.
************************************************************/
  IF DAY(@start_dt) = 30 AND DAY(@end_dt) = 31
  BEGIN
    SET @end_dt = DATEADD(d, -1, @end_dt)
  END

  --Setup the Result
  SET @dpd = (DATEDIFF(m, @start_dt, @end_dt) * 30) + (DAY(@end_dt) - DAY(@start_dt))

  RETURN @dpd

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 10:31:51

Missing function MIS_Dept.dbo.fnGetLastDayOfMonth...

Also calculates these intervals incorrectly

source		target		Excel	SSR
--------	--------	-----	----
20000131	20040229	1469	1471
20000229	20040229	1439	1441
20000331	20040229	1409	1411

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 10:42:41

CPU:
You average at 678 ms for a sample table with 65170 records.
I average at 353 ms for a sample table with 65170 records.

Duration:
You average at 2919 ms for a sample table with 65170 records.
I average at 1503 ms for a sample table with 65170 records.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources