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.

2018-09-04 23_29_56-Window

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

2018-09-04 23_22_39-Window

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:

2018-09-04 23_26_19-Window

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:

2018-09-05 16_25_18-Window

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:

2018-09-05 16_30_02-Window

 

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.

2018-09-05 16_32_46-Window

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.

2018-09-05 16_36_59-Window

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:

2018-09-05 16_41_34-Window

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.

2018-09-05 16_46_57-Window

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.

2018-09-06 14_53_40-Date_Time_manipulations.sql - geningsql07.ETL (CORP_tomazk (115)) - Microsoft SQ

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.

2018-09-05 16_52_51-Window

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.

2018-09-05 16_53_37-Window

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.

2018-09-05 22_23_06-Window

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.

2018-09-05 22_23_17-Window

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:

2018-09-07 21_19_46-Window

Thank you for the comments and suggestions.

 

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

Happy T-SQLing.

Tagged with: , , , , , , ,
Posted in Uncategorized
3 comments on “T-SQL date and time manipulation
  1. […] Tomaz Kastrun walks us through various functions to work with dates and times in T-SQL: […]

    Like

  2. Ben Oastler says:

    Some really great stuff in here. And would be really useful for creating your date and time dimensions. Only other thing I’d suggest is calculating fiscal year given a starting month.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories
Follow TomazTsql on WordPress.com
Programs I Use
Programs I Use
Programs I Use
Rdeči Noski – Charity

Rdeči noski

100% of donations made here go to charity, no deductions, no fees. For CLOWNDOCTORS - encouraging more joy and happiness to children staying in hospitals (http://www.rednoses.eu/red-noses-organisations/slovenia/)

€2.00

Top SQL Server Bloggers 2018
TomazTsql

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

Discover

A daily selection of the best content published on WordPress, collected for you by humans who love to read.

Revolutions

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

tenbulls.co.uk

attaining enlightenment with sql server, .net, biztalk, windows and linux

SQL DBA with A Beard

He's a SQL DBA and he has a beard

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

Search Msdn

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

R-bloggers

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World

R-bloggers

R news and tutorials contributed by hundreds of R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

SQL Server, Azure and DLM in a nutshell :D

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.

%d bloggers like this: