SQL date format functions like the DateDiff SQL function and DateAdd SQL Function are oft used by DBAs but many of us never took the time to fully understand these extremely useful features. For professionals just getting started with SQL Server, these functions are some of the first to become familiar with. So hopefully this article will have a little something for everyone across the skill spectrum

One of the most interesting data types that are supported in the relational database world is DateTime. In this article, we’re going to take a look at working with date time data types in SQL Server. We’ll understand the basics of date-time data-type and also, we’ll see a various examples of how to query the date-time fields using built-in functions within SQL Server for manipulating the data, transforming date-time values and in few cases, perform arithmetic operations.

First, let’s go ahead take a look at some popular SQL date format and time functions.

By default, SQL Server inherently supports the languages that are supported by the Windows Operating System. Depending on locale and collation settings when you do the installation of SQL Server, the SQL date format display may be different. The kind of the data you’re going to play is determined based on locale setting and SQL Server collation setting.

In SQL Server, the data type DATE has two default Neutral Language Formats

1. ‘YYYYMMDD’

   The following example, the HumanResource.Employee table is queried to see the SQL date format of the HireDate values

   1 2 3 4 5 6

   USE AdventureWorks2014; GO   SELECT HireDate,BusinessEntityID,LoginID, JobTitle FROM HumanResources.Employee; GO

   Now, query the HireDate column using neutral language format. In the query, we’re passing integer value that consists of year, 2008; month, 12 and the 7^th day.

   1 2 3

   SELECT HireDate,BusinessEntityID,LoginID, JobTitle FROM HumanResources.Employee WHERE HireDate = '20081207';

   
   

   

   Note: It basically means that the input value is numeric; the SQL engine internally converts it and actually ends up with a character value. So, it is surrounded by the single quotes

2. ‘MM-DD-YYYY’ ( US-Based Format)

   In this SQL date format, the HireDate column is fed with the values ‘MM-DD-YYYY’. This is not an integer format. Let us run the same query with the new SQL date format of input value ‘12-07-2008’ to the HireDate column. So let’s run the following query and verify the output. The output is the same set of five records.

   1 2 3 4

   SELECT HireDate,BusinessEntityID,LoginID, JobTitle FROM HumanResources.Employee WHERE HireDate = '12-07-2008'; GO

   
   

   

   Note: The neutral language format values are implicit and SQL Server will do the necessary conversion. Any other SQL date format, requires a proper conversion of operands or/and values.

The following examples display an error message due to the improper use of the SQL date format.

Now, the operand and its values are both converted to a standard format 103. You can refer here for more information about CAST and Convert functions. The following example returns all the rows of the employee where HireDate ’25-12-2008’

Now let’s start taking a look at some of the date functions that SQL Server makes available for us and these date functions allow us to work with the different parts of the dates and even manipulate some of the functionality that we can work with in terms of dates.

Let’s look at the DATEPART SQL function. This function returns an integer value from the specified date column

Query the HireDate column to return only the year portion of the date

The following example generates an integer output column named [Year Part], and this should return only the year values

Query the HireDate column to return only the month portion of the date

The following example generates an integer output column named [Month Part], and this should return only the month values

Query the HireDate column to return only the Quarter portion of the date

The following example generates an integer output column named [Month Part], and this should return only the month values

The following example returns the sales details for the specific day of the orderDate field. The example also shows the usage of the DATEPART SQL function and its few associated arguments.

The SELECT statement selects the DATEPART, which is the function name, and it takes two arguments inside the parentheses. First, the datepart argument and the second, the date expression.

Let’s take a look at using the DATENAME SQL function. This is similar to a DATEPART SQL function, but it returns a character string from the specified date field.

Next, let’s take a look at the deterministic functions that returns date and time parts as an integer value. The following example returns integer values from the OrderDate field for the corresponding YEAR and MONTH functions.

The output is an aggregated value of the TotalSales based on monthly sales.

Now let’s take a look at a couple of other different functions. And this one we’re going to look at is called DATEDIFF. The DATEDIFF SQL function returns a signed integer value that allows us to determine elapsed time between two dates.

Let us play with the query to use different date-parts to see the results.

The following example returns the time parts between the two DateTime fields. In this example, hours, minutes, and seconds between the DateTime values are calculated using DATEDIFF SQL function

How to use the DATEDIFF SQL function in the where clause

The following example returns all the employees who are working with the organization for more than 10 years. The conditional logic on the HireDate column is compared with greater than 120 months is mentioned in the where clause.

How to use the DateDiff SQL function with an Aggregate function

The following example returns the number of years between the first hire date and the last hire date. In this case, we’re looking for the minimum HireDate and the maximum HireDate aggregate function used as input parameters for DATEDIFF SQL function. With these values, one could easily find the number of years existed between the first time hire and the last time hire.

Let’s take a look at the DATEADD SQL function. The functions add or subtract the value to the specified datepart and return the modified value of the datepart.

Let’s take a look at the following example. The value 1 is added to the various datepart and the value -1 is added to the hour datepart and -30 are added to minute datepart.

In the output, we can see that the value is operated on the GETDATE() function as per the defined datepart.

Note: DATEADD and DATEDIFF SQL function can be used in the SELECT, WHERE, HAVING, GROUP BY and ORDER BY clauses.

Datadiff vs Datediff_BIG

DATEDIFF	DATEDIFF_BIG
This is a very old function and it is available from the initial release SQL Server.	With the birth of SQL Server 2016, Microsoft has introduced a new DATEDIFF SQL function, an enhanced version of DATEDIFF known as DATEDIFF_BIG.
Syntax DATEDIFF(datepart, start_date, end_date)	Syntax DATEDIFF_BIG(datepart,start_date,end_date)
The return type of the DATEDIFF SQL function is INTEGER(4 bytes)	The return type of the DATEDIFF_BIG SQL function is BIGINT(8 bytes)
This function returns an integer value represents an elapsed time or day based on the defined DATEPART between the specified start DateTime and end DateTime	This function returns a BIG integer value represents an elapsed time or day based on the defined DATEPART between the specified start DateTime and end DateTime
The integer value ranges between -2,147,483,648 to +2,147,483,647	The BIGINT datatype can store a value between the range -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
Example: This example demonstrates the behavior of the DATEDIFF SQL function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value. 1 SELECT DATEDIFF(MILLISECOND, '20180101', '20181231') AS 'DIFF DATA MILLISECOND'; Error Message: The DATEDIFF SQL function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.	Example: This example demonstrates the behavior of the DATEDIFF_BIG SQL function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value. 1 SELECT DATEDIFF_BIG(MILLISECOND, '20180101', '20181231') AS 'DIFF DATA MILLISECOND';
1 2 3 4 5 6 7 8 9 10 11 12 13 14 DECLARE @StartDate datetime2 = '20180101 00:00:00.0000000'; DECLARE @EndDate datetime2 = DATEADD(year, 100, @StartDate);   SELECT   'DATEDIFF',     DATEDIFF( year,@StartDate , @EndDate ) AS Years,   DATEDIFF( quarter, @StartDate, @EndDate ) AS Quarters,     DATEDIFF( month, @StartDate, @EndDate ) AS Months,     DATEDIFF( week, @StartDate, @EndDate ) AS Weeks,     DATEDIFF( dayofyear, @StartDate, @EndDate ) AS DayOfYear,     DATEDIFF( day, @StartDate, @EndDate ) AS Days,    DATEDIFF(MILLISECOND, @StartDate, @EndDate ) AS Millsecond,    DATEDIFF(MICROSECOND, @StartDate, @EndDate ) AS Micro,    DATEDIFF(NANOSECOND, @StartDate, @EndDate ) AS Nano	1 2 3 4 5 6 7 8 9 10 11 12 13 14 DECLARE @StartDate datetime2 = '20180101 00:00:00.0000000'; DECLARE @EndDate datetime2 = DATEADD(year, 100, @StartDate);   SELECT   'DATEDIFF_BIG',     DATEDIFF_BIG( year,@StartDate , @EndDate ) AS Years,   DATEDIFF_BIG( quarter, @StartDate, @EndDate ) AS Quarters,     DATEDIFF_BIG( month, @StartDate, @EndDate ) AS Months,     DATEDIFF_BIG( week, @StartDate, @EndDate ) AS Weeks,     DATEDIFF_BIG( dayofyear, @StartDate, @EndDate ) AS DayOfYear,     DATEDIFF_BIG( day, @StartDate, @EndDate ) AS Days,    DATEDIFF_BIG(MILLISECOND, @StartDate, @EndDate ) AS Millsecond,    DATEDIFF_BIG(MICROSECOND, @StartDate, @EndDate ) AS Micro,    DATEDIFF_BIG(NANOSECOND, @StartDate, @EndDate ) AS Nano

That’s all for now on this SQL date format article …

Wrap Up

Thus far, we have seen very important SQL date format functions such as DATEPART, DATENAME, YEAR, MONTH, and DAY, with particular emphasis on the DATEADD SQL function and the DATEDIFF SQL function. SQL date formatfunctions in SQL server are really powerful and really helps to deliver impactful data analytics and reports. In some cases, it’s a matter of adjusting collation settings, location settings, simply readjusting the input stream of text value, or correction made the data source would suffice the date time problem.

It is recommended to use the neutral language format because it works better in most cases. At last, we see the difference between DATEDIFF SQL function and DATEDIFF_BIG SQL function. I hope you like this article. If you have any questions, feel free comment below

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021