Tag Archives: Sql Server

How to get difference between two dates in Years, Months and days in Sql Server

Sql Server, , , , , , , , , , , , , , , , ,

Many a time we come across a scenario where we need to calculate the difference between two dates in Years, Months and days in Sql Server. In this article we will see how we can achieve this. This article covers the following:

  • Difference between two dates in Years
  • Difference between two dates in Months
  • Difference between two dates in Days
  • Difference between two dates in X years Y Months and Z days (For example: Age in Years, Months and days)

1. Difference between two dates in Years

Approach 1:

We can use DATEDIFF() function like below to get the difference between two dates in Years in Sql Server. DATEDIFF() functions first parameter value can be year or yyyy or yy all will return the same result. Below example shows how we can get difference between two dates in Years

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
 DATEDIFF(YEAR, @FromDate, @ToDate)
   -
 (CASE
   WHEN DATEADD(YEAR, 
           DATEDIFF(YEAR, @FromDate,@ToDate), @FromDate)
       > @ToDate THEN 1 
         ELSE 0 END) 'Date difference in Years'

RESULT:
Difference between dates in years

Approach 2:

You may be thinking why such a complex logic is used to calculate the difference between two dates in years in Apporach 1 instead of using just a single DATEDIFF() function. The reason for using such a complex logic is, DATEDIFF() function returns the number of boundaries crossed by the specified datepart between the specified fromdate and enddate. Basically it calculates the difference between two dates by ignoring all the dateparts smaller than the specified datepart from both the dates. Let us understand this with an example

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
 DATEDIFF(YEAR, @FromDate, @ToDate) 'Date difference in Years'

RESULT:
Date Difference in Years using DateDiff function

From the above result it is clear that even-though @ToDate is the next day from the @FromDate, but still the difference returned is 1 year. So based on the need use the appropriate approach. If we need to calculate age, then we should be using the Approach1.

2. Difference between two dates in Months

We can use DATEDIFF() function like below to get the difference between two dates in Months in Sql Server. DATEDIFF() functions first parameter value can be month or mm or m all will return the same result. Below example shows how we can get difference between two dates in Months

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
   (DATEDIFF(MONTH, @FromDate, @ToDate)
   -
   (CASE WHEN DATEADD(MONTH, 
        DATEDIFF(MONTH, @FromDate, @ToDate), @FromDate)
       > @ToDate
     THEN 1 ELSE 0 END)) 'Date difference in Months'

RESULT:
Difference between dates in months

Approach 2:

You may be thinking why such a complex logic is used to calculate the difference between two dates in months in Apporach 1 instead of using just a single DATEDIFF() function. The reason for using such a complex logic is, DATEDIFF() function returns the number of boundaries crossed by the specified datepart between the specified fromdate and enddate. Basically it calculates the difference between two dates by ignoring all the dateparts smaller than the specified datepart from both the dates. Let us understand this with an example

DECLARE @FromDate DATETIME = '2014-12-31', 
        @ToDate DATETIME = '2015-01-01'
SELECT @FromDate 'From Date', @ToDate 'To Date',
 DATEDIFF(MONTH, @FromDate, @ToDate) 'Date difference in Months'

RESULT:
Date Difference in Months using DateDiff function

From the above result it is clear that even-though the @ToDate is the next day from the @FromDate, but still the difference returned is 1 Month. So based on the need use the appropriate approach.

3. Difference between two dates in Days

We can use DATEDIFF() function like below to get the difference between two dates in Years in Sql Server. DATEDIFF() functions first parameter value can be year or yyyy or yy all will return the same result. Below example shows how we can get difference between two dates in Years

DECLARE @FromDate DATETIME = '2015-07-10 23:59:59.000', 
        @ToDate   DATETIME = '2015-07-11 00:00:00.000'
SELECT @FromDate 'From Date', @ToDate 'To Date',
   (DATEDIFF(DAY, @FromDate, @ToDate)
   -
   (CASE WHEN DATEADD(DAY, 
        DATEDIFF(DAY, @FromDate, @ToDate), @FromDate)
       > @ToDate
     THEN 1 ELSE 0 END)) 'Date difference in Days'

RESULT:
Difference between dates in days

Approach 2:

You may be thinking why such a complex logic is used to calculate the difference between two dates in days in Apporach 1 instead of using just a single DATEDIFF() function. The reason for using such a complex logic is, DATEDIFF() function returns the number of boundaries crossed by the specified datepart between the specified fromdate and enddate. Basically it calculates the difference between two dates by ignoring all the dateparts smaller than the specified datepart from both the dates. Let us understand this with an example

DECLARE @FromDate DATETIME = '2015-07-10 23:59:59.000', 
        @ToDate   DATETIME = '2015-07-11 00:00:00.000'
SELECT @FromDate 'From Date', @ToDate 'To Date',
   DATEDIFF(DAY, @FromDate, @ToDate) 'Date difference in Days'

RESULT:
Date Difference in Days using DateDiff function

From the above result it is clear that even-though the @ToDate is the next second from the @FromDate, but still the difference returned is 1 year. So based on the need use the appropriate approach.

4. Difference between two dates in X years Y Months and Z days (For example: Age in Years, Months and days)

We can use a script like below to get the difference between two dates in Years, Months and days.

DECLARE @FromDate DATETIME = '2010-01-01 23:59:59.000', 
        @ToDate   DATETIME = '2015-01-02 00:00:00.000',
        @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
 - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
          @FromDate) > @ToDate THEN 1 ELSE 0 END) 

   
SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
 - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
          @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
   
SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)
 - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
          @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
   
SELECT @FromDate FromDate, @ToDate ToDate, 
       @Years Years,  @Months Months, @Days Days

RESULT:
Difference between dates in years months and days in sql

We can wrap the above script into a scalar function like below and can be reused for calculating the difference between dates

 
CREATE FUNCTION dbo.GetDateDifference
(
   @FromDate DATETIME, @ToDate DATETIME
)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
    SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
     - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
              @FromDate) > @ToDate THEN 1 ELSE 0 END) 
   
    SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
    SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
   
    SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
    SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
  
  
    RETURN 'Years: ' + CAST(@Years AS VARCHAR(4)) +
            ' Months: ' + CAST(@Months AS VARCHAR(2)) +
            ' Days: ' + CAST(@Days AS VARCHAR(2)) 
END
GO

Below example shows how we can use the above function in the select statement

DECLARE @FromDate DATETIME = '2010-01-01 23:59:59.000', 
        @ToDate   DATETIME = '2015-01-02 00:00:00.000'
SELECT @FromDate FromDate, @ToDate ToDate, 
       dbo.GetDateDifference(@FromDate, @ToDate) 
           AS 'Difference between dates'

RESULT:
Difference between dates in years months and days by scalar function in sql

We can as-well create a multi-statement table valued function like below to get difference between two dates in years, months and days

CREATE FUNCTION dbo.GetDateDifferenceInYearsMonthsDays
(
    @FromDate DATETIME, @ToDate DATETIME
)
RETURNS
 @DateDifference TABLE (
 YEAR INT,  MONTH INT, DAYS INT)
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT, @tmpFromDate DATETIME
    SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
     - (CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate),
              @FromDate) > @ToDate THEN 1 ELSE 0 END) 
   
    SET @tmpFromDate = DATEADD(YEAR, @Years , @FromDate)
    SET @Months =  DATEDIFF(MONTH, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
   
    SET @tmpFromDate = DATEADD(MONTH, @Months , @tmpFromDate)
    SET @Days =  DATEDIFF(DAY, @tmpFromDate, @ToDate)
     - (CASE WHEN DATEADD(DAY, DATEDIFF(DAY, @tmpFromDate, @ToDate),
              @tmpFromDate) > @ToDate THEN 1 ELSE 0 END) 
   
    INSERT INTO @DateDifference
    VALUES(@Years, @Months, @Days)
   
    RETURN
END

Below example shows how we can use the above function in the select statement

DECLARE @FromDate DATETIME = '2010-01-01 23:59:59.000', 
        @ToDate   DATETIME = '2015-01-02 00:00:00.000'
SELECT * 
FROM dbo.GetDateDifferenceInYearsMonthsDays(@FromDate,@ToDate)
GO

RESULT:
Difference between dates in years months and days by table valued function in sql

How to get Yearly data in Sql Server

Scripts, Sql Server, , , , , , , , , ,

This article demonstrate how to get yearly data in Sql Server in different formats as shown in the below image. Here Sales table data is presented in two different yearly aggregated sales data formats.

Yearly data in Sql Server

Let us create a Sales table and insert 1000 Sample Sales Records With Random sales date in the past 5 years using the below script.

CREATE DATABASE SqlHintsYearlyData
GO
USE SqlHintsYearlyData
GO
--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With 
--Random past 0 to 5 years as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(YEAR, - ROUND(5 * RAND(), 0),GETDATE()))
GO 1000

Demo 1: Getting Yearly Data by using Group By

SELECT YEAR(SalesDate) [Year], Count(1) [Sales Count]   
FROM #Sales
GROUP BY YEAR(SalesDate)
ORDER BY 1

RESULT:
Yearly data in Sql Server by Group By

Demo 2: Getting Yearly data using Static PIVOT

[ALSO READ] PIVOT and UNPIVOT in Sql Server

SELECT *
FROM (SELECT YEAR(SalesDate) [Year],
      Count(1)  [Sales Count]   
FROM #Sales
GROUP BY YEAR(SalesDate)) AS YearlySalesData
PIVOT( SUM([Sales Count])   
    FOR [Year] IN ([2010],[2011],[2012],
 [2013],[2014],[2015])) AS YearPivot

RESULT:
Yearly data in Sql Server by Pivot

Demo 3: Getting Yearly data using Dynamic PIVOT

[ALSO READ] Dynamic PIVOT in Sql Server

Problem with Demo 2 approach is , it is using the static PIVOT to get the data. Which requires the developer to specify all the years as the pivot columns manually. We can re-write the Demo 2 examples by using Dynamic pivot approach as below which doesn’t require the developer to manually specify the years column.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT YEAR(SalesDate) [Year],  Count(1)  [Sales Count]   
	INTO #PivotSalesData
FROM #Sales
GROUP BY YEAR(SalesDate) 
--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Year])
FROM (SELECT DISTINCT [Year] FROM #PivotSalesData) AS Years
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT ' + @ColumnName + '
    FROM #PivotSalesData
    PIVOT(SUM( [Sales Count]   ) 
          FOR [Year] IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

RESULT:
Yearly data in Sql Server by Pivot

[ALSO READ]:
How to get Quarterly Data in Sql Server
How to get Monthly Data in Sql Server
How to get Daily data in Sql Server
How to get Hourly data in Sql Server

How to get Daily data in Sql Server

Scripts, Sql Server, , , , , , , , , , ,

This article demonstrate how to get daily data in Sql Server in different formats as shown in the below image. Here Sales table data is presented in two different daily aggregated sales data formats.

Daily data report in Sql Server

Let us create a Sales table and insert 1000 Sample Sales Records With Random sales date in the past 5 days using the below script.

CREATE DATABASE SqlHintsDailyData
GO
USE SqlHintsDailyData
GO
--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With 
--Random past 0-5 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(DAY, - ROUND(5 * RAND(), 0),GETDATE()))
GO 1000

Demo 1: Getting Daily Data by using Group By

SELECT CAST(SalesDate AS DATE) [Date], 
   Count(1)  [Sales Count]   
FROM #Sales
GROUP BY CAST(SalesDate AS DATE)
ORDER BY 1

RESULT:
Daily Data in Sql Server by Group By

Demo 2: Getting Daily data using Static PIVOT

[ALSO READ] PIVOT and UNPIVOT in Sql Server

SELECT *
FROM (SELECT CAST(SalesDate AS DATE) [Date], 
             Count(1)  [Sales Count]   
      FROM #Sales
      GROUP BY CAST(SalesDate AS DATE)) AS DailyData
PIVOT( SUM([Sales Count])  
    FOR [Date] IN ([2015-06-29],[2015-06-30],[2015-07-01],
  [2015-07-02],[2015-07-03],[2015-07-04])) AS DatePivot

RESULT:
Daily Data in Sql Server by Pivot

Demo 3: Getting Daily data using Dynamic PIVOT

[ALSO READ] Dynamic PIVOT in Sql Server

Problem with Demo 2 approach is , it is using the static PIVOT to get the data. Which requires the developer to specify all the dates as the pivot columns manually. We can re-write the Demo 2 examples by using Dynamic pivot approach as below which doesn’t require the developer to manually specify the dates column.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT CAST(SalesDate AS DATE) [Date], Count(1)  [Sales Count] 
	INTO #PivotSalesData  
FROM #Sales
GROUP BY CAST(SalesDate AS DATE)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Date])
FROM (SELECT DISTINCT [Date] FROM #PivotSalesData) AS Dates
 
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT ' + @ColumnName + '
    FROM #PivotSalesData
    PIVOT(SUM( [Sales Count]   ) 
          FOR [Date] IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

RESULT:
Daily Data in Sql Server by Pivot
[ALSO READ]:
How to get Yearly data in Sql Server
How to get Quarterly Data in Sql Server
How to get Monthly Data in Sql Server
How to get Hourly data in Sql Server