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

License: Free
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]
( 
				@dwstart datetime, @dwend datetime,@extraDay bit
)
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;

Rate

4 (7)

Share

Share

Rate

4 (7)