# Date calculation

,

There are situations when it is necessary to determine the age of the patient in the form yy mm dd. Or when it is necessary to determine the length of service of employees. Also in the form yy mm dd. The natural solution for this is .Net coding. But sometimes it is necessary to achieve a solution through T-SQL. This script serves it.

```SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID('dbo.CalcDate') IS NOT NULL
BEGIN
DROP FUNCTION dbo.CalcDate;
END;
GO

/*********************************************************************************************
Dates calculation in form yy mm dd
2016 Darko Martinović

There are situations when it is necessary to determine the age of the patient in the form yy mm dd. Or when it is necessary to determine the length of service of employees. Also in the form yy mm dd. The natural solution for this is .Net coding. But sometimes it is necessary to achieve a solution through T-SQL. This script serves it. The script works by SQL Server 2005+ versions

Feedback: mailto:darko.martinovic@outlook.com

Examples :
SELECT dbo.CalcDate(NULL, GetDate(),0) -> NULL
SELECT dbo.CalcDate(GetDate(),GetDate(),0) -> 0 0 0
SELECT dbo.CalcDate(GetDate(),GetDate(),0) -> 0 0 1
SELECT dbo.CalcDate('20150101','20161003',0) -> 1 9 2
SELECT dbo.CalcDate('20031101', '20161003',0) ->12 11 2
SELECT [dbo].[CalcDate]('20040731', '20040601', 0) -> 0 1 30
SELECT [dbo].[CalcDate]('20040731', '20040601', 1) -> 0 2 0

*********************************************************************************************/

CREATE FUNCTION [dbo].[CalcDate]
(
)
RETURNS nvarchar(20)
BEGIN
DECLARE @yy int;
DECLARE @mm int;
DECLARE @dd int;
DECLARE @increment int;
SET @increment = 0;
DECLARE @monthDay TABLE
(
monthno int, monthdayno int
);
DECLARE @dStart AS datetime;
DECLARE @dEnd AS datetime;
INSERT INTO @monthDay
VALUES (1, 31);
INSERT INTO @monthDay
VALUES (2, -1);
INSERT INTO @monthDay
VALUES (3, 31);
INSERT INTO @monthDay
VALUES (4, 30);
INSERT INTO @monthDay
VALUES (5, 31);
INSERT INTO @monthDay
VALUES (6, 30);
INSERT INTO @monthDay
VALUES (7, 31);
INSERT INTO @monthDay
VALUES (8, 31);
INSERT INTO @monthDay
VALUES (9, 30);
INSERT INTO @monthDay
VALUES (10, 31);
INSERT INTO @monthDay
VALUES (11, 30);
INSERT INTO @monthDay
VALUES (12, 31);
--The order of the arguments is not important
IF @dwStart > @dWEnd
BEGIN
SET @dStart = @dWEnd;
SET @dEnd = @dWStart;
END;
ELSE
BEGIN
SET @dStart = @dWStart;
SET @dEnd = @dWEnd;
END;
--

DECLARE @d1 AS INT;
SET @d1 = DAY(@dStart);
DECLARE @d2 AS int;
SET @d2 = DAY(@dEnd);
IF @d1 > @d2
BEGIN
SET @increment = (SELECT
monthdayno
FROM @monthDay
WHERE monthno = MONTH(@dStart));
END;

IF @increment = -1
BEGIN
--Is it a leap year
SET @increment = (SELECT
CASE
WHEN ISDATE(CAST(YEAR(@dStart) AS CHAR(4)) + '0229') = 1 THEN 29
ELSE 28
END);
END;

IF @increment != 0
BEGIN
SET @DD = DAY(@dEnd) + @increment - DAY(@dStart) + (CASE
WHEN @extraDay = 1 THEN 1
ELSE 0
END);
SET @increment = 1;
END;
ELSE
BEGIN
SET @dd = DAY(@dEnd) - DAY(@dStart) + (CASE
WHEN @extraDay = 1 THEN 1
ELSE 0
END);
END;
IF (MONTH(@dStart) + @increment) > MONTH(@dEnd)
BEGIN
SET @mm = MONTH(@dEnd) + 12 - (MONTH(@dStart) + @increment);
SET @increment = 1;
END;
ELSE
BEGIN
SET @mm = MONTH(@dEnd) - (MONTH(@dStart) + @increment);
SET @increment = 0;
END;
SET @yy = YEAR(@dEnd) - (YEAR(@dStart) + @increment);
IF @dd >= 31
BEGIN
SET @mm = @mm + 1;
SET @dd = @dd - 31;
END;

IF @mm >= 12
BEGIN
SET @yy = @yy + 1;
SET @mm = @mm - 12;
END;

RETURN (CONVERT(NVARCHAR(2), @yy) + ' ' + CONVERT(NVARCHAR(2), @mm) + ' ' + CONVERT(NVARCHAR(2), @dd));

END;```

4 (7)

4 (7)