T-SQL date and time manipulation

Manipulating date and time in T-SQL is a daily and very common task that every DBA, SQL Developer, BI Developer and data scientist will come across. And over the years, I have accumulated many of the simple date or/and time manipulation combinations of different functions, that it is time, to put them together.

Don’t expect to find here anything you haven’t used or seen – especially, if you are a long time T-SQL developer. The point is to have a post, that will have a lot of examples on date and time manipulation on one place. And by no means, this is not the definite list, but should be quite substantial and the code on Github repository will be update.

The list will be updated on my Github, and therefore this blogpost might not include all. In all of the following examples I will be using function GETDATE() to get the current datetime, unless the examples will have stored dates. Therefore, some of the examples or screen-prints will be different from yours.

I will be primarily using following T-SQL functions:

• dateadd
• datediff
• year
• month
• day
• format
• cast
• convert
• parsename
• and some string manipulation functions like: substring, replicate, left, right.

Starting with simple date

Most common functions are for sure extracting Year, Month and Day from your date.

SELECT
   GETDATE() AS RightNow
 ,YEAR(GETDATE()) AS Year_RightNow
 ,MONTH(GETDATE()) AS Month_RightNow
 ,DAY(GETDATE()) AS Day_RightNow

Deciding if the Year is a leap or non-leap year, following examples will help you get along:

-- Is Leap Year (we check for presence/existence of 29.Feb)
SELECT
    ISDATE('2019/02/29') AS CheckLeapYear -- if 1, date exists and this year is a leap year; if 0 date does not exists and is not leap year
   ,CASE WHEN (YEAR(GETDATE()) % 4 = 0 AND YEAR(GETDATE()) % 100 <> 0) 
  OR YEAR(GETDATE()) % 400 = 0 THEN 'Leap Year' ELSE 'Non Leap Year' END AS CheckLeapYear

with results as following:

So the first example will simply check if the 29th of February is valid for given year or not. But for a given SQL Statement the second variant with modulo for years is much easier for execution.

The following block of examples will simply give you the desired dates for a given time period. Mainly using functions DATENAME, DATEPART, DATEDIFF, CAST, CONVERT, FORMAT and DAY, MONTH, WEEK, YEAR, EOMONTH.

Simply run and examine the examples to get the hang of the examples.

-- Name of Days, Months
SELECT 
DATENAME(WEEKDAY, GETDATE()) AS [DayName]
,DATENAME(MONTH, GETDATE()) AS [MonthName]
,DATEPART(WEEK, GETDATE()) AS [WeekNumber]
,DATEPART(ISO_WEEK, GETDATE()) AS [ISO_WeekNumber]

-- Using Date format or FORMAT or CAST / CONVERT
SELECT
CAST(GETDATE() AS DATE) AS Date_RightNow
,FORMAT(GETDATE(), 'dd/MM/yyyy') AS DATE_dd_MM_yyyy
,FORMAT(GETDATE(), 'yyyy-MM-dd') AS DATE_yyyy_MM_dd
,FORMAT(GETDATE(), 'MM-dd') AS DATE_MM_dd
,FORMAT(GETDATE(), 'dd/MM/yyyy', 'en-US' ) AS DATE_US 
,FORMAT(GETDATE(), 'dd/MM/yyyy', 'sl-SI' ) AS DATE_SLO

-- Days
SELECT
DATEADD(DAY,DATEDIFF(DAY,1,GETDATE()),0) AS Yesterday
,DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0) AS Today
,DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),1) AS Tomorrow

-- Weeks
SELECT
DATEADD(WEEK,DATEDIFF(WEEK,7,GETDATE()),0) AS LastWeek_startOf
,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0) AS ThisWeek_startOf
,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),7) AS NextWeek_startOf

-- Months (works for all months; with 30 or 31 days, or with February)
SELECT
DATEADD(MONTH,DATEDIFF(MONTH,31,GETDATE()),0) AS LastMonth_startOf
,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AS ThisMonth_startOf
,DATEADD(MONTH,DATEDIFF(MONTH,-1,GETDATE()),0) AS NextMonth_startOf

SELECT 
EOMONTH(GETDATE()) AS CurrentMonthEnd
,EOMONTH(GETDATE(), -1) AS PreviousMonthEnd
,EOMONTH(GETDATE(), 1) AS NextMonthEnd

-- Years (works with leap years)
SELECT
DATEADD(year, DATEDIFF(year, 365, (GETDATE())), 0) AS LastYear_startOf
,DATEADD(year, DATEDIFF(year, 0, (GETDATE())), 0) AS ThisYear_startOf
,DATEADD(year, DATEDIFF(year, -1, (GETDATE())), 0) AS NextYear_startOf

And the results are as following:

I have added in all of the examples the extraction of the current, previous and next part of the date (week, day, month, year).

Getting dates in particular point-in-time

Sometimes one needs to set a particular time in the future / past and setting a particular point-in-time. Therefore, I Have added just a simple example for finding exact time from now.

SELECT
DATEADD(MONTH, DATEDIFF(MONTH, -1, (GETDATE())), DAY(GETDATE()-1)) AS OneMonthFromNow
,CAST(DATEADD(MONTH, 1, CAST(GETDATE() AS DATE)) AS DATETIME) AS OneMonthFromNow


SELECT
CAST(DATEADD(YEAR, 1, CAST(GETDATE() AS DATE)) AS DATETIME) AS OneYearFromNow
,DATEADD(DAY, DATEDIFF(DAY, 0, (GETDATE())), 365) AS OneYearFromNow

Finding point-in-time can also be used with for example also using BETWEEN function.

The result of the query is:

 

Differences in dates

When you are solving and calculating the differences in dates, I have added a simple “countdown” with the remaining number of days until end of month or year.

-- Number of days until ...
SELECT
	 (DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, -1, (GETDATE())), 0)))-1 AS NumberOfDAysUntilEndOfMonth
	,(DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, -1, (GETDATE())), 0)))-1 AS NumberOfDAysUntilEndOfYear

The results can be replaced with calculation of hours, quarters, years.

But more interesting and sometimes not that straightforward business case would be the calculation of working days – so finding the difference between two dates and calculating the number or working day.

The following example does this with simple query:

-- Number of business / working days between two dates
SET DATEFIRST 1;
DECLARE @dayFrom SMALLDATETIME = '20180901'
DECLARE @dayTO SMALLDATETIME = '20180905'

SELECT
(DATEDIFF(DAY, @dayFrom, @dayTO) + 1)-(DATEDIFF(WEEK, @dayFrom, @dayTO) * 2)-(CASE WHEN DATEPART(WEEKDAY, @dayFrom) = 7 THEN 1 ELSE 0 END)-(CASE WHEN DATEPART(WEEKDAY, @dayTO) = 6 THEN 1 ELSE 0 END) AS NumberOfWorkingDays

And since I have added that the week starts on Monday (SET DATEFIRST 1), and that 1st of September 2018 was Saturday, one can easily calculate this. But dateframe can be extended and problem is not that trivial anymore.

But making yet such trivial problem little bit more complicated, another typical business case is to calculate (sum/count) working hours between two dates. Now, the trickery can now go, from simple to over-complicated. Following example uses CTE (Common Table Expression) with recursions.

So the calculation is fairly simple:

-- Number of working hours between two dates
SET DATEFIRST 1;
DECLARE @dayFromDateTime SMALLDATETIME = '2018-09-01 12:33:11.245'
DECLARE @dayTODateTime SMALLDATETIME = '2018-09-05 09:33:32.256'
DECLARE @hourFrom INT = 8
DECLARE @hourTo INT = 16

;WITH cte
AS
(SELECT
DATEADD(MINUTE, -1, @dayFromDateTime) AS StartDate
,0 AS WorkDayFlag
,0 AS WorkHourFlag

UNION ALL

SELECT
DATEADD(MINUTE, 1, StartDate) AS StartDate
,CASE WHEN DATEPART(WEEKDAY, DATEADD(MINUTE, 1, StartDate)) IN (1,2,3,4,5) THEN 1 ELSE 0 END AS WorkDayFlag
,CASE WHEN DATEPART(HOUR, DATEADD(MINUTE, 1, StartDAte)) BETWEEN @hourFrom AND @hourTo-1 THEN 1 ELSE 0 END AS WorkHourFlag
FROM cte
WHERE
StartDate <= @dayTODateTime
)
SELECT
SUM(CASE WHEN WorkDayFlag = 1 AND WorkHourFlag = 1 THEN 1 ELSE 0 END) AS nofWorkingMinutes
,SUM(CASE WHEN WorkDayFlag = 1 AND WorkHourFlag = 1 THEN 1 ELSE 0 END)*1.0/60 AS nofWorkingHours
FROM cte 
OPTION (MAXRECURSION 10000)

 

I have even added the time – when business hours begin and end within a given time frame. Please note that I do not support the usage of MAXRECURSION OPTIONS but for small examples the query option is very effective.

The result of the query is:

If you will be using and running such calculations extensively, I strongly suggest to change the solution to UDF and adding some additional rules table with list of holidays, working days / weekends, setting up the start of the week, getting correct times (timezone, UTC) and any other business rules that should be applied.

Date intervals

Finding correct intervals is always part of  solving the business need. By querying the database, one will for sure come across the intervals as start and end date/time format.

Here it the example collection of date intervals between current week, month, quarter and script can be extended to different or broader (narrower) interval.

SELECT 
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '18991231') as FromCurrentWeek
,DATEADD(WEEK, DATEDIFF(WEEK, '18991231', GETDATE()), '19000106') as ToCurrentWeek

SELECT 
CAST(DATEADD(DAY, 1, EOMONTH(GETDATE(),-1)) AS DATETIME) AS FromCurrentMonth
,CAST(EOMONTH(GETDATE()) AS DATETIME) AS ToCurrentMonth

SELECT 
DATEADD(QUARTER, DATEDIFF(QUARTER, 0, (GETDATE())), 0) as FromStartCurrentQuarter
,DATEADD(DAY,-1,DATEADD(QUARTER, DATEDIFF(QUARTER, -1, (GETDATE())), 0)) as ToEndCurrentQuarter

--Last days
SELECT 
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS LastDayOfPreviousMonth
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) AS LastDayOfCurrentMonth
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) AS LastDayOfNextMonth

And the results of the query based on the GETDATE() function.

werwerwe

Support Date functions

With following couple of examples, you can set your current session settings, e.g.: language, start of the week (DATEFIRST), dateformat (DATEFORMAT) and based on these settings, the behaviour of inserts or selects against any datetime data types can be altered, controlled or changed.

-- Set language
SET LANGUAGE us_english;
SELECT DATENAME(WEEKDAY, '20190904') [US_English];
 
SET LANGUAGE Slovenian;
SELECT DATENAME(WEEKDAY, '20180904') [Slovenian];

-- Starting week with day
SET LANGUAGE Slovenian;  
SELECT @@DATEFIRST AS weekStart;  

SET LANGUAGE us_english;  
SELECT @@DATEFIRST AS WeekStart;  

-- datefirst or @@datefirst
SET DATEFIRST 1; -- week starts with monday

-- Example with  September 5th, 2018
-- Possible formats:  mdy, dmy, ymd, ydm, myd, and dym. 
DROP TABLE IF EXISTS #dateformats
CREATE TABLE #dateformats (dd SMALLDATETIME ,ddFormat VARCHAR(10))

SET DATEFORMAT mdy;
INSERT INTO #dateformats  VALUES ('09/06/2018', 'mdy');
SET DATEFORMAT ymd;
INSERT INTO #dateformats  VALUES ('2018/09/07', 'ymd');
SET DATEFORMAT dmy;
INSERT INTO #dateformats  VALUES ('08/09/2018', 'ymd');

SELECT 
	 dd AS InsertedDate
	,ddFormat AS InsertedDateFormat
FROM #dateformats

With SET DATEFORMAT, example shows, that if datetime format data types come in different format year-month-day or day-month-year, by simple controlling of SET DATEFORMAT, date formats can be handled easier, without any futher CASTing or CONVERTing.

Starting Simple Time

Manipulating time, as part of datetime data type or a standalone time, following examples will help you understand working with time. Simple functions as HOUR, MINUTE, SECOND or MILLISECOND will extract part of the time for any further use.

Also CONVERT and CAST are used frequently for converting time to desired format.

SELECT
  GETDATE() AS RightNow
 ,DATEPART(HOUR,GETDATE()) AS Hour_RightNow
 ,DATEPART(MINUTE, GETDATE()) AS Minute_RightNow
 ,DATEPART(SECOND, GETDATE()) AS Second_RightNow
 ,DATEPART(MILLISECOND, GETDATE()) AS MilliSecond_RightNow

-- Using Date format or FORMAT or CAST / CONVERT To get only time
SELECT
  CAST(GETDATE() AS TIME) AS TIME_RightNow
 ,CONVERT(CHAR,GETDATE(),14) AS TIME_withConvert14
 ,CONVERT(TIME, GETDATE()) AS Time_ConvertONly
 ,CAST(CONVERT(CHAR(8),GETDATE(),114) AS DATETIME) AS Time_WithConvert114_AandDate
 ,CONVERT(VARCHAR(12),GETDATE(),114) AS Time_standardFormat
 ,GETUTCDATE() AS TimeUTC_RightNow
 ,SYSDATETIME() AS SystemDateTime
 ,CONVERT(VARCHAR(10), CAST(GETDATE() AS TIME(0)), 100) AS SimpleAM_PM

SELECT
  FORMAT(cast(GETDATE() AS TIME), N'hh\.mm') AS timeFormatDot
 ,FORMAT(cast(GETDATE() AS TIME), N'hh\:mm') AS timeFormatColon
 ,FORMAT(CAST(GETDATE() AS TIME), 'hh\:mm\:ss') AS standardTimeFormat

 

Query outputs the following results and using function FORMAT might be fast and easy handling time formats for desired outputs.

Converting to time formats

Time can be stored in different type formats, which might present a problem, but firstly, let’s take a look into integer.

Following example shows how to manipulate the time formats, when time parts are stored in integers.

-- seconds/milliseconds to time format
DECLARE @MilliSec INT = 55433
DECLARE @Sec INT = 532

SELECT 
CONVERT(VARCHAR(10),DATEADD(ms,@MilliSec,0),114) AS MilliSecToTime --format hh:mm:ss:nnn
,CONVERT(VARCHAR(10),DATEADD(s,@Sec,0),114) AS SecToTime --format hh:mm:ss:nnn

-- Converting seconds to time readable format
DECLARE @seconds INT = 10000

SELECT
@seconds AS NumberOfSeconds
,@seconds/86400 AS NumberOfDays
,@seconds/3600 As NumberOfHours
,@seconds/60 AS NumberMinutes
,CONVERT(VARCHAR, DATEADD(ms, @seconds * 1000, 0), 114) AS FormatedTime

Both examples are converting integers into time formats for readable and usable form.

VARCHAR / DECIMAL/FLOAT to time formats

Oh. Yes. This happens more often than you think. Having datetime information stored in DECIMAL, FLOAT is not that infrequent case. Let us not go into the bad designs, but rather propose the idea, on how to tackle such problems.

With decimal data type:

-- Using Decimal data type
DECLARE @test_DTY TABLE
(id int
,KA2_DATE decimal (8,0)
,KA2_TIME decimal (6,0)
)

INSERT INTO @test_DTY
SELECT 1, 20180905, 110951
UNION ALL SELECT 2, 20180905, 113407
UNION ALL SELECT 3, 20180905, 063407

SELECT
id
,KA2_DATE AS OriginalDate
,KA2_TIME AS OriginalDate --Note leading zeros will not be presented as this is decimal data type

,RIGHT(CAST(KA2_DATE AS VARCHAR(8)),2) + '/' + SUBSTRING(CAST(KA2_DATE AS VARCHAR(8)),5,2) + '/' + LEFT(CAST(KA2_DATE AS VARCHAR(8)),4) AS DateFormated_dd_MM_yyyy
,LEFT(CAST(REPLICATE('0', 6 - LEN(CAST(KA2_TIME AS VARCHAR(6)))) AS VARCHAR(1)) + CAST(CAST(KA2_TIME AS VARCHAR(6)) AS VARCHAR(6)),2) + ':' 
+ SUBSTRING(CAST(REPLICATE('0', 6 - LEN(CAST(KA2_TIME AS VARCHAR(6)))) AS VARCHAR(1)) + CAST(CAST(KA2_TIME AS VARCHAR(6)) AS VARCHAR(6)),3,2) + ':' 
+ RIGHT(CAST(REPLICATE('0', 6 - LEN(CAST(KA2_TIME AS VARCHAR(6)))) AS VARCHAR(1)) + CAST(CAST(KA2_TIME AS VARCHAR(6)) AS VARCHAR(6)),2) AS Time_formatted_hh_mm_ss
FROM @test_DTY

you can see the results are presented in more readable and easy to handle formats for any further use with your T-SQL query.

Another frequent, yet uncomfortable data type is VARCHAR format. With use of PARSENAME and other string manipulation functions (SUBSTRING, CHARINDEX, PATINDEX, …), converting to datetime formats can be done. Even though, it is not performance efficient, sometimes you just can not change the table(s) design.

-- Using String data Type (Varchar)
DECLARE @temp TABLE (Ddate VARCHAR(20))

INSERT INTO @temp (Ddate)
SELECT '3.11.2017 14:55:53' 
UNION ALL SELECT '12.11.2018 22:39:49' 
UNION ALL SELECT '12.8.2018 22:39:49' 
UNION ALL SELECT '2.3.2018 22:39:49' 
UNION ALL SELECT '12.8.2018 7:39:49' 
UNION ALL SELECT '12.8.2018 7:09:49'

SELECT 
Ddate AS OriginalDateFormatInVarchar
,PARSENAME(Ddate,3) AS DayFromVarchar
,PARSENAME(Ddate,2) AS MonthFromVarchar
,LEFT(PARSENAME(Ddate,1),4) AS YearFromVarchar
,REPLACE(SUBSTRING(Ddate, CHARINDEX(' ', Ddate), CHARINDEX(':', Ddate)),':','') AS TimeFromVarchar

,CAST(CAST(LEFT(PARSENAME(Ddate,1),4) AS CHAR(4)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,2) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,2) AS VARCHAR(2)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,3) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,3) AS VARCHAR(2)) AS INT) AS DateFormattedFromVarchar --Leading zeros corrected!

,CAST(CAST(LEFT(PARSENAME(Ddate,1),4) AS CHAR(4)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,2) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,2) AS VARCHAR(2)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,3) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,3) AS VARCHAR(2)) AS SMALLDATETIME) AS DateFormattedFromVarcharToSmallDateTime

,CAST(CAST(left(PARSENAME(Ddate,1),4) AS CHAR(4)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,2) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,2) AS VARCHAR(2)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,3) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,3) AS VARCHAR(2)) + REPLICATE('0', 6 - LEN(
CAST(LTRIM(RTRIM(REPLACE(SUBSTRING(Ddate, CHARINDEX(' ', Ddate), CHARINDEX(':', Ddate)),':',''))) AS VARCHAR(6)))) +
CAST(LTRIM(RTRIM(REPLACE(SUBSTRING(Ddate, CHARINDEX(' ', Ddate), CHARINDEX(':', Ddate)),':',''))) AS VARCHAR(6))
AS VARCHAR(14)) AS DateAndTimeFormatFromVarchar

FROM @temp
ORDER BY 8 ASC

Using the query, the end result is time (and date) represented in different formats for any further use with T-SQL query or functions for datetime formats, like between and logical operators.

Fiscal Years (#Update 1; Sept. 6th,2018)

For any given time (using GETDATE()), getting the start and end time of the fiscal year is another important boundary for and PnL or financial reports.

-- a) it start on April 1 and runs for 364,25 days until March 31 of next year 
-- b) it starts on October 1 and runs for 364,25 days until September 30 of next year

SELECT
-- Fiscal Year Apr-Mar
CASE WHEN MONTH(GETDATE()) >= 4 THEN YEAR(GETDATE()) ELSE DATEPART(year,DATEADD(Year,-1,GETDATE())) END AS StartOfFiscalYearYear
,CASE WHEN MONTH(GETDATE()) >= 4 THEN YEAR(GETDATE())+1 ELSE YEAR(GETDATE()) END AS EndOfFiscalYearYear
,CASE WHEN MONTH(GETDATE()) >= 4 THEN CAST(CONCAT(CAST(YEAR(GETDATE()) AS VARCHAR),'/04/01') AS DATE) ELSE CAST(CONCAT(CAST(DATEPART(year,DATEADD(Year,-1,GETDATE())) AS VARCHAR),'/04/01') AS DATE) END AS StartOfFiscalYearDateTime
,CASE WHEN MONTH(GETDATE()) >= 4 THEN CAST(CONCAT(CAST(YEAR(dateadd(year,1,getdate())) AS VARCHAR),'/03/31') AS DATE) ELSE CAST(CONCAT(CAST(YEAR(GETDATE()) AS VARCHAR),'/03/31') AS DATE) END AS EndOfFiscalYearDateTime
-- Fiscal Year Oct-Sep
,CASE WHEN MONTH(GETDATE()) >= 10 THEN YEAR(GETDATE()) ELSE DATEPART(year,DATEADD(Year,-1,GETDATE())) END AS StartOfFiscalYearYear
,CASE WHEN MONTH(GETDATE()) >= 10 THEN YEAR(GETDATE())+1 ELSE YEAR(GETDATE()) END AS EndOfFiscalYearYear
,CASE WHEN MONTH(GETDATE()) >= 10 THEN CAST(CONCAT(CAST(YEAR(GETDATE()) AS VARCHAR),'/10/01') AS DATE) ELSE CAST(CONCAT(CAST(DATEPART(year,DATEADD(Year,-1,GETDATE())) AS VARCHAR),'/10/01') AS DATE) END AS StartOfFiscalYearDateTime
,CASE WHEN MONTH(GETDATE()) >= 10 THEN CAST(CONCAT(CAST(YEAR(dateadd(year,1,getdate())) AS VARCHAR),'/09/30') AS DATE) ELSE CAST(CONCAT(CAST(YEAR(GETDATE()) AS VARCHAR),'/09/30') AS DATE) END AS EndOfFiscalYearDateTime

Using two different boundaries for fiscal year and given the fact that today is September 6th, 2018, the result is as following:

Thank you for the comments and suggestions.

 

As always, all the code is available at the Github.

Happy T-SQLing.

‹ Top SQL Server bloggers of 2018

T-SQL job title generator ›

Tagged with: cast, convert, data types, date, Microsoft, SQL Server, T-SQL, time
Posted in Uncategorized