Adding, subtracting, rounding and calculating differences , Sql Server

Adding, subtracting, rounding and calculating differences

--------------------------------------------------------------------------------
-- Dates table
--------------------------------------------------------------------------------
CREATE TABLE testing_date_list
(
    TheDate DATE NOT NULL PRIMARY KEY
);

--------------------------------------------------------------------------------
-- Custom Date Range Using Number sequence
--------------------------------------------------------------------------------

DECLARE @DateStart DATE = '01-DEC-17';
DECLARE @DateEnd   DATE = '07-FEB-18';
 
PRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);
PRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);

WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation
  FROM number_tbl t1
  CROSS JOIN number_tbl t2
  CROSS JOIN number_tbl t3
)
INSERT
  INTO testing_date_list
      (TheDate)
SELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]
  FROM number_sequences
 WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd
 ORDER BY concatenate_calculation
;

--------------------------------------------------------------------------------
-- Addition, Subtraction & Rounding
--------------------------------------------------------------------------------
SELECT [TheDate],
     --Adding & Subtracting
       DateAdd(DAY, 1, [TheDate]) AS TheDate_DateAdd_1_day,
       DateAdd(DAY,-1, [TheDate]) AS TheDate_DateAdd_sub_1_day,
     --Rounding
       DateAdd(MONTH, DateDiff(MONTH, 0, [TheDate]), 0)    AS TheDate_WholeMonth,
       DateAdd(YEAR,  DateDiff(YEAR,  0, [TheDate]), 0)    AS TheDate_WholeYear,
       DateFromParts(YEAR([TheDate]), MONTH([TheDate]), 1) AS TheDate_WholeMonth_DFP,
     --Subtracting and Rounding
       DateAdd(MONTH, -3, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)) AS TheDate_ThreeMonthsBeforeStart,
       DateAdd(MONTH,-13, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)) AS TheDate_PriorYearMonthOnMonth   -- 1 year of months from last month
  FROM testing_date_list
;

--------------------------------------------------------------------------------
-- Difference
--------------------------------------------------------------------------------
SELECT [TheDate],
     --Difference
       DateDiff(DAY,   GetDate(), [TheDate]) AS DiffFromNow_Days,
       DateDiff(MONTH, GetDate(), [TheDate]) AS DiffFromNow_Months,
       DateDiff(YEAR,  GetDate(), [TheDate]) AS DiffFromNow_Years,
       DateDiff(HOUR,  GetDate(), [TheDate]) AS DiffFromNow_Hours
  FROM testing_date_list
;

--------------------------------------------------------------------------------
-- Difference between start and end
-- 750 seconds is 12.5 minits
--------------------------------------------------------------------------------
DECLARE @DateTimeStart DATETIME2(4) = DateAdd(SECOND, -750, GetDate());
DECLARE @DateTimeEnd   DATETIME2(4) = GetDate();

SELECT @DateTimeStart AS [start],
       @DateTimeEnd   AS [end],
       DateDiff(SECOND, @DateTimeStart, @DateTimeEnd) AS DiffInSeconds,
     --Note: the minutes are rounded up here
       DateDiff(MINUTE, @DateTimeStart, @DateTimeEnd) AS DiffInMinutes,
     --To format the time:
     -- - Calculate the number of seconds
     -- - Multiply that by 1000 so we get milliseconds
     -- - Add to 0
     -- - Only output the time portion
       CONVERT(VARCHAR, DateAdd(MILLISECOND, DateDiff(SECOND, @DateTimeStart, @DateTimeEnd) * 1000, 0), 114) AS FormattedTime
;


run \| edit \| history \| help	0