Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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 ; GO -------------------------------------------------------------------------------- -- 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
Please
log in
to post a comment.
TSQL - Unique string generator
Max from multiple columns
CREATE DATABASE
Test Link samll Data
Duplicate Records
SQL Stringdata
string concatenation as aggregate operator in group by
Jogarao1
Names
QLSV
Please log in to post a comment.