SQL DATEADD() | DATE_ADD()

The SQL DATEADD() | DATE_ADD() is a function, and use to add or subtract a number of time units to a date.

The SQL DATEADD() | DATE_ADD() function is use to perform an arithmatic operations on a date time value. We can specify the interval value to be added or subtracted from the given input date time value.

It can be used in SELECT statement as well in where clause.

Example 1: You’d like to add a given number of days to a date in SQL.

Our database has a table named Flight with data in the columns Code and DepartureDate.

Code	DepartureDate
LT2030	2019-02-20
GH1100	2019-03-01
SR5467	2019-12-30

Let's move all flight departure dates forward two days from the current time.

To indicate the unit of time to add, provide the amount to add, and choose the new date, we will utilise the DATEADD() function. Look at this problem:

SELECT Code,
   DATEADD(day, 30, DepartureDate)
   AS ChangedDepartureDate
FROM Flight;

Output:

Code	ChangedDepartureDate
LT2030	2019-03-22
GH1100	2019-03-03
SR5467	2020-01-01

Discussion:

Use SQL Server's DATEADD() function to modify a date and/or time by adding a certain number of a selected unit of time. Date, time, or date and time data types are supported by this function. There are three justifications:

• The appropriate date/time unit to add. In this case, the time is day; we want to increase the date by a day.
• The number of units to add. In our example, this is 2, thus we want to extend the date by two days.
• A column holding the desired date, time, and datetime. (In this case, the DepartureDate field is used.) A return date, time, or datetime expression can also be used as this argument.

Example 2: To add 30 days to a value in the table, you can use ADDDATE() function with UPDATE command. The syntax is as follows:

UPDATE yourTableName
SET yourDateColumnName=ADDDATE(yourDateColumnName,INTERVAL 30 DAY);

To understand the above syntax, let us create a table. The query to create a table is as follows:

Step 1: Create Table

mysql> create table Add30DayDemo
 (
 Id int NOT NULL AUTO_INCREMENT,
 ShippingDate date,
 PRIMARY KEY(ID)
 );

Step 2: Insert Data

Insert some records in the table using insert command. The query is as follows:

insert into Add30DayDemo(ShippingDate) values('2019-02-04');
insert into Add30DayDemo(ShippingDate) values('2018-11-15');
insert into Add30DayDemo(ShippingDate) values('2013-05-18');
insert into Add30DayDemo(ShippingDate) values('2017-08-25');
insert into Add30DayDemo(ShippingDate) values('2016-03-01');
insert into Add30DayDemo(ShippingDate) values('2015-09-03');

Step 3: Display records

Display all records from the table using select statement. The query is as follows:

select *from Add30DayDemo;

Output:

Id  ShippingDate 
1    2019-02-04   
2    2018-11-15   
3    2013-05-18   
4    2017-08-25   
5    2016-03-01   
6    2015-09-03   

Step 4: Here is the query to add 30 days value in a table using DATEADD():

update Add30DayDemo
SET ShippingDate=ADDDATE(ShippingDate,INTERVAL 30 DAY);Q

Step 5: Now check the table records once again. The query is as follows:

select *from Add30DayDemo;

Output:

Id  ShippingDate 
1    2019-03-06   
2    2018-12-15   
3    2013-06-17   
4    2017-09-24   
5    2016-03-31    
6    2015-10-03 

Use the DATEADD() method in SQL Server to add hours to DateTime. Hour or hh can be used as the first parameter value in the DATEADD() function; both will provide the same outcome.

Example 1: This example shows how we can add two hours to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
   DATEADD(hour,2,GETDATE()) 'Now + 2 Hours'
SELECT GETDATE() 'Now',
   DATEADD(hh,2,GETDATE()) 'Now + 2 Hours'

Result :

Now                                 Now +2 hours
2014-07-06 01:07:38.380            2014-07-06 03:07:38.380   
2014-07-06 01:07:38.380            2014-07-06 03:07:38.380 

Example 2: To add hours to an Oracle date you can this simple query:

select sysdate, sysdate + (1/24*5) "5 hours" from dual;

The formula (1/24*5) is explained as follows:

• sysdate + 1 is one day ahead (exactly 24 hours)
• - divided by 24 gives one hour
• * 5 multiply by 5 to get the 5 hours

You can also add 5 hours to a date this way: (60 * 5)

select sysdate, sysdate + interval '300' "5 hours" from dual;

As we see, there are several ways to add hours to an Oracle date column.

Example 3: To add hours to date in sql server we need to write the query like as shown below:

declare @hours int;
set @hours = 5;
select  getdate() as currenttime, dateadd(HOUR, @hours, getdate()) as timeadded

When we run above query we will get output like as shown below

Output:

Currenttime                    timeadded
2015-06-06 23:19:38.087       2015-06-06 04:19:38.087

Use the DATEADD() function in SQL Server to add months to dates and times. Month, mm, or m are all acceptable first parameter values for the DATEADD() function; all will provide the same output.

Example 1: The DATEADD() function will return the last day of the return month if you add a number of months to a date and the day of the date result is missing.

SELECT 
    DATEADD(month, 4, '2019-05-31') AS result;

The return date in this instance is in the month of September. Day 30 of September is returned by the DATEADD() function as the day for the result date because day 31 does not exist in September.

Output:

Example 2: Below example shows how we can add two months to Current DateTime in Sql Server:

SELECT GETDATE() 'Today', 
   DATEADD(month,2,GETDATE()) 'Today + 2 Months'
SELECT GETDATE() 'Today',
   DATEADD(mm,2,GETDATE()) 'Today + 2 Months'
SELECT GETDATE() 'Today',
   DATEADD(m,2,GETDATE()) 'Today + 2 Months'

Result:

Today                              Today +2 Months
2014-04-05 01:10:36.37           2014-06-05 01:10:36.37

Calculating the first business day after a certain date is a frequent situation that you may have experienced. As a bonus, you'll learn about recursion, the new LEAD(), LAG(), and accumulation in T-SQL. There are quite a number unpleasant ways to tackle this, including cursors. However, there are also some fairly neat ways to approach the problem.

The first business day beginning with a specific date can be determined in two different ways. An initial assumption is that we will simply advance the date by one day if the "current" day is a holiday. We'll need to create a recursive function that supports numerous bank holidays in succession, such as Christmas or other significant holidays, because if it's a Saturday, you'd finish up on a Sunday.

Example 1: Any solution you create must be designed around a table that includes knowledge of the dates that are bank holidays or business days because SQL Server does not automatically know these dates. For each date that isn't a business day, I've decided to create a single record in a table called simply BankHolidays with a single date column.

BankHoliday table:

CREATE TABLE dbo.BankHolidays (
    [date]        date NOT NULL,
    CONSTRAINT PK_BankHolidays PRIMARY KEY CLUSTERED ([date])
);

Swedish bank holidays for december 2013 and january 2014:

INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-01'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-07'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-08'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-14'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-15'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-21'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-22'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-24'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-25'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-26'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-28'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-29'});
INSERT INTO dbo.BankHolidays VALUES ({d '2013-12-31'});
INSERT INTO dbo.BankHolidays VALUES ({d '2014-01-01'});
INSERT INTO dbo.BankHolidays VALUES ({d '2014-01-04'});
INSERT INTO dbo.BankHolidays VALUES ({d '2014-01-05'});
INSERT INTO dbo.BankHolidays VALUES ({d '2014-01-06'});
INSERT INTO dbo.BankHolidays VALUES ({d '2014-01-11'});
INSERT INTO dbo.BankHolidays VALUES ({d '2014-01-12'});
INSERT INTO dbo.BankHolidays VALUES ({d '2014-01-18'});
INSERT INTO dbo.BankHolidays VALUES ({d '2014-01-19'});
INSERT INTO dbo.BankHolidays VALUES ({d '2014-01-25'});
INSERT INTO dbo.BankHolidays VALUES ({d '2014-01-26'});

1. An iterating scalar function

Here is a pretty straightforward scalar function solution. Once the input date has been assigned to a variable, that variable is then raised by one day each time a row from the bank holiday table is missed.

CREATE FUNCTION dbo.fn_firstBusinessDay(@date date)
RETURNS date
AS

BEGIN;
  --- The iteration/output variable:
  DECLARE @businessDay date;
  SELECT @businessDay=@date;

  --- Increment the value by one day if this
  --- is a bank holiday:
  WHILE (EXISTS (SELECT [date]
   FROM dbo.BankHolidays
   WHERE [date]=@businessDay))
   SET @businessDay=DATEADD(dd, 1, @businessDay);

  --- Done:
  RETURN @businessDay;
END;

Naturally, this function will execute an index seek in the BankHolidays table for each iteration, making it a less-than-ideal choice in terms of performance. It gets worse if you add it to a huge batch of entries since then every record and iteration will hit the BankHolidays table with a seek operation.

2. Using a recursive CTE

A better idea that comes to mind, given that this is a recursive problem, is to use a recursive common table expression.

ALTER FUNCTION dbo.fn_firstBusinessDay(@date date)
RETURNS date
AS

BEGIN;
 --- The output variable:
 DECLARE @businessDay date;

 --- Recursive common table expression:
 WITH rcte ([date], nextBusinessDay)
 AS (
 
 --- The anchor is the input date (if it's a bank holiday)
 SELECT [date], DATEADD(dd, 1, [date]) AS nextBusinessDay
 FROM dbo.BankHolidays
 WHERE [date]=@date
 UNION ALL
 --- ... and the recursion adds a day to the "nextBusinessDay" column as long as it's
 --- still a bank holiday:
 SELECT h1.[date],
 DATEADD(dd, 1, h1.nextBusinessDay) AS nextBusinessDay
 FROM rcte AS h1
 INNER JOIN dbo.BankHolidays AS h2 ON
 h1.nextBusinessDay=h2.[date]
 )

 --- Finally, the result of the CTE will contain one record for each iteration, 
 --- so we'll filter out the "last" one, which is the one with the latest 
 --- "nextBusinessDay", hence the MAX() function:
 SELECT @businessDay=MAX(nextBusinessDay)
 FROM rcte
 WHERE [date]=@date

 --- But if @date wasn't a bank holiday from the start, we'll just return the original date:
 IF (@businessDay IS NULL)
 SET @businessDay=@date;

 RETURN @businessDay;
END;

Instead of repeatedly running SELECT operations on the BankHolidays table, SQL Server may execute a single table scan and then deal with the data using a table spool, a type of internal temporary table, which significantly boosts performance. But what if you could completely stop the recursions and iterations.

3. Using the LEAD() function

Example 1: Some of the new T-SQL features introduced with SQL Server 2012 are the LEAD() and LAG() functions, which let you view the prior or subsequent column value in a recordset. Look at the following example:

SELECT [date] AS bankHoliday,
   LEAD([date], 1)
   OVER (ORDER BY [date]) AS nextHoliday
FROM dbo.BankHolidays;

The BankHolidays table's rows' dates as well as the following bank holiday are returned by this query. This is advantageous because every day between this bank holiday and the next is a working day (although some rows will have zero days between the two).

Example 2: We can create a list of "work weeks" if we combine the aforementioned query into a subquery and isolate just bank holidays where the next day is a business day. A "work week" is defined in this context as a continuous run of one or more business days.

SELECT
    LAG(nextHoliday, 1, {d '1900-01-01'})
        OVER (ORDER BY bankHoliday) AS lastHoliday,
    DATEADD(dd, 1, bankHoliday) AS startBusinessDay,
    DATEADD(dd, -1, nextHoliday) AS endBusinessDay
FROM (
    SELECT [date] AS bankHoliday,
        LEAD([date], 1, {d '2099-12-31'})
            OVER (ORDER BY [date]) AS nextHoliday
    FROM dbo.BankHolidays) AS sub
WHERE DATEADD(dd, 1, bankHoliday)< nextHoliday;

The first column represents the day after the prior "week," while the second and third columns represent the beginning and final working days of the previous "week." You'll see that the day following "endBusinessDay" corresponds to "lastHoliday" on the row below.

Example 3: Once more using the scalar function, the following data is calculated using the "weeks" common table expression:

ALTER FUNCTION dbo.fn_firstBusinessDay(@date date)
RETURNS date
AS

BEGIN;
 DECLARE @businessDay date;
  --- This CTE contains "weeks" where each week starts with
  --- with one or several bank holidays and ends on the day
  --- before the next bank holiday.
  WITH weeks (lastHoliday, startBusinessDay, endBusinessDay)
  AS (
   SELECT
   --- The first bank holiday of the previous week:
   LAG(nextHoliday, 1, {d '1900-01-01'})
   OVER (ORDER BY bankHoliday) AS lastHoliday,
   --- The first business day of this week:
   DATEADD(dd, 1, bankHoliday) AS startBusinessDay,
   --- The last business day of this week:
   DATEADD(dd, -1, nextHoliday) AS endBusinessDay
   FROM (
   SELECT [date] AS bankHoliday,
   LEAD([date], 1, {d '2099-12-31'})
   OVER (ORDER BY [date]) AS nextHoliday
   FROM dbo.BankHolidays) AS sub
   --- Skip sequential holidays:
   WHERE DATEADD(dd, 1, bankHoliday)< nextHoliday
   )

   --- The first business day is the first business day
   --- of a week that includes @date between "lastHoliday"
   --- and "endBusinessDay".
   --- If @date is before the first business day, we'll
   --- use startBusinessDay, otherwise @date is fine.
   SELECT @businessDay=(CASE WHEN @date< startBusinessDay
   THEN startBusinessDay
   ELSE @date END)
   FROM weeks
   WHERE lastHoliday<=@date AND @date<=endBusinessDay;

   RETURN @businessDay;
END;

So far, we’ve eliminated iterations and recursive queries, but there’s still one major performance eye-sore left: Because fn_firstBusinessDay is a scalar function (i.e. it returns a single value for each call), applying it to a large dataset is going to be costly, because it’ll query the dbo.BankHolidays table once for every record.

4. A set-based solution

Example 1: The "weeks" table that we're utilising in the LEAD/LAG solution must be extracted and joined directly to the recordset that we wish to use it on. Although I'm making an inline table function in this example, you could very well place this query in a view.

CREATE FUNCTION dbo.fn_workWeeks()
RETURNS TABLE
AS
 RETURN (
 SELECT
--- The first bank holiday of the previous week:
 LAG(nextHoliday, 1, {d '1900-01-01'})
 OVER (ORDER BY bankHoliday) AS lastHoliday,
--- The first business day of this week:
 DATEADD(dd, 1, bankHoliday) AS startBusinessDay,
--- The last business day of this week:
 DATEADD(dd, -1, nextHoliday) AS endBusinessDay
 FROM (
 SELECT [date] AS bankHoliday,
 LEAD([date], 1, {d '2099-12-31'})
 OVER (ORDER BY [date]) AS nextHoliday
 FROM dbo.BankHolidays) AS sub
--- Skip sequential holidays:
 WHERE DATEADD(dd, 1, bankHoliday)< nextHoliday
 );

Example 2: All that remains now is to JOIN this dataset to your table and apply the CASE condition, just like we did in the scalar function:

SELECT *, (CASE WHEN someDates.[date]< startBusinessDay
   THEN startBusinessDay
   ELSE someDates.[date] END)
FROM someDates
INNER JOIN dbo.fn_workWeeks() AS w ON
   lastHoliday<=someDates.[date] AND
   someDates.[date]<=endBusinessDay;

5. The cumulative count approach

Example 1: Here is yet another method for solving the issue. Creating a list of all the dates will be a good place to start. We'll create a CTE called "holidays" that holds the dates of each bank holiday as well as the date of the following one using the LEAD function. You will be familiar with this strategy from before:

WITH holidays ([date], nextDate)
AS (
  SELECT [date],
  LEAD([date], 1) OVER (ORDER BY [date]) AS nextDate
  FROM dbo.BankHolidays)

SELECT [date], nextDate
FROM holidays
ORDER BY 1;

Example 2: A recursive CTE made up of integers between 0 and, say, 10 can be added to this. We may create a full calendar with all the dates by adding the integer's number of days to the "date" column of the "holidays" CTE.

WITH holidays ([date], nextDate)
AS (
 SELECT [date],
 LEAD([date], 1) OVER (ORDER BY [date]) AS nextDate
 FROM dbo.BankHolidays),

 iterator (i)
AS (
 SELECT 0 AS i UNION ALL
 SELECT i+1 FROM iterator WHERE i<10)

SELECT DATEADD(dd, i.i, h.[date]) AS [date]
FROM holidays AS h
INNER JOIN iterator AS i ON
    i.i< DATEDIFF(dd, h.[date], h.nextDate)
ORDER BY 1;

Example 3: Let’s LEFT JOIN “holidays” again, to see which days are bank holidays and which ones are not:

WITH holidays ([date], nextDate)
AS (
 SELECT [date],
 LEAD([date], 1) OVER (ORDER BY [date]) AS nextDate
 FROM dbo.BankHolidays),

 iterator (i)
AS (
 SELECT 0 AS i UNION ALL
 SELECT i+1 FROM iterator WHERE i<10)

SELECT
 DATEADD(dd, i.i, h.[date]) AS [date],
 (CASE WHEN bh.[date] IS NULL THEN 1 ELSE 0 END) AS isBusinessDay
FROM holidays AS h
INNER JOIN iterator AS i ON
 i.i< DATEDIFF(dd, h.[date], h.nextDate)
LEFT JOIN holidays AS bh ON
 DATEADD(dd, i.i, h.[date])=bh.[date]
ORDER BY 1;

So, using the CASE block, we're determining whether or not the given date is a business day. All that is left to accomplish at this point is to compute a running total for the "isBusinessDay" column. A running total will function like a ROW_NUMBER() because this column only contains the values 1:s and 0:s, with the exception that it will only increase for rows that are business days.

Example 4: With the ROWS UNBOUNDED PRECEDING keywords and the SUM() windowed function, running totals are computed as follows:

WITH iterator (i)
AS (
 SELECT 0 AS i UNION ALL
 SELECT i+1 FROM iterator WHERE i<10),

 holidays ([date], nextDate)
AS (
 SELECT [date], LEAD([date], 1) OVER (ORDER BY [date]) AS nextDate
 FROM dbo.BankHolidays)

SELECT
 DATEADD(dd, i.i, h.[date]) AS [date],
 (CASE WHEN bh.[date] IS NULL THEN 1 ELSE 0 END) AS isBusinessDay,
--- Accumulated "businessDayNumber" over dates:
 SUM((CASE WHEN bh.[date] IS NULL THEN 1 ELSE 0 END)) OVER (
 ORDER BY DATEADD(dd, i.i, h.[date])
 ROWS UNBOUNDED PRECEDING) AS businessDayNumber
FROM holidays AS h
INNER JOIN iterator AS i ON
 i.i< DATEDIFF(dd, h.[date], h.nextDate)
LEFT JOIN holidays AS bh ON
 DATEADD(dd, i.i, h.[date])=bh.[date]
ORDER BY 1;

Example 5: We can utilise the "business day sequence number" found in the resulting "businessDayNumber" column in our calculation. Let's first wrap the aforementioned code in a useful inline table function:

CREATE FUNCTION dbo.fn_businessDays()
RETURNS TABLE
AS
 RETURN (
 WITH iterator (i)
 AS (
  SELECT 0 AS i UNION ALL
  SELECT i+1 FROM iterator WHERE i<10),

  holidays ([date], nextDate)
  AS (
  SELECT [date],
  LEAD([date], 1) OVER (ORDER BY [date]) AS nextDate
  FROM dbo.BankHolidays)

  SELECT
  DATEADD(dd, i.i, h.[date]) AS [date],
  (CASE WHEN bh.[date] IS NULL
  THEN 1 ELSE 0 END) AS isBusinessDay,
--- Accumulated "businessDayNumber" over dates:
  SUM((CASE WHEN bh.[date] IS NULL
  THEN 1 ELSE 0 END)) OVER (
  ORDER BY DATEADD(dd, i.i, h.[date])
  ROWS UNBOUNDED PRECEDING) AS businessDayNumber
  FROM holidays AS h
  INNER JOIN iterator AS i ON
  i.i< DATEDIFF(dd, h.[date], h.nextDate)
  LEFT JOIN holidays AS bh ON
  DATEADD(dd, i.i, h.[date])=bh.[date]);

Example 6: This function can be used to determine how many business days there are between two dates. Please take note that this method allows us to calculate dates beyond the following business day!

Four business days from 2013-12-23:

SELECT a.[date] AS fromDate,
    b.businessDayNumber-a.businessDayNumber AS businessDays,
    b.[date] AS toDate
FROM dbo.fn_businessDays() AS a
INNER JOIN dbo.fn_businessDays() AS b ON
    b.isBusinessDay=1
WHERE a.[date]={d '2013-12-23'} AND
    b.businessDayNumber-a.businessDayNumber=4;

Three business days before 2014-01-05:

SELECT a.[date] AS fromDate,
b.businessDayNumber-a.businessDayNumber AS businessDays,
b.[date] AS toDate
FROM dbo.fn_businessDays() AS a
INNER JOIN dbo.fn_businessDays() AS b ON
a.isBusinessDay=1
WHERE b.[date]={d '2014-01-05'} AND
b.businessDayNumber-a.businessDayNumber=3;

How many business days from 2013-12-13 to 2014-01-02?

SELECT a.[date] AS fromDate,
b.businessDayNumber-a.businessDayNumber AS businessDays,
b.[date] AS toDate
FROM dbo.fn_businessDays() AS a
CROSS JOIN dbo.fn_businessDays() AS b
WHERE a.[date]={d '2013-12-13'} AND
b.[date]={d '2014-01-02'};

Example 2: I often receive a very common question during my Comprehensive Database Performance Health Check about how to find Find Business Days Between Dates. Let us see a very simple script for it.

DECLARE @StartDate AS DATE = '2021-07-01', @EndDate AS DATE = '2021-07-31'
;WITH dateCTE AS
(
  SELECT @StartDate StartDate, DATEPART(WEEKDAY,@StartDate) wkday
  UNION ALL
  SELECT DATEADD(day,1,StartDate) ,  DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday  
  FROM dateCTE
  WHERE DATEADD(day,1,StartDate) <= @EndDate
)
SELECT COUNT(*) WeekDays 
FROM dateCTE 
WHERE wkday NOT IN(1,7) -- Execluding Sat, Sun

Result:

Any other date can be used as the start date and end date, and you will still get reliable results. Please comment on this blog post on finding business days between dates and let me know what you think.

Example 3: There are a huge amount of results if you search the web for how to figure out how many business days there are between two dates in SQL Server. Here is another with an accompanying explanation.

Examples of specifications:

The business requirement might be something like this: "Today, I am calculating the number of days between two dates to get the response time in days but I really would like to omit the weekends to get an accurate image."

I am going to simulate this business scenario below where it shows an activity’s start and end date:

WITH response_time
AS
(
 SELECT
 GETDATE() - 600 AS start_date,
 GETDATE() - 500 AS end_date
)
SELECT a.*,
DATEDIFF(day, a.start_date, a.end_date) AS reponse_time_in_days
FROM response_time a;

Result:

START_DATE	END_DATE	 REPONSE_TIME_IN_DAYS
7/1/2012 15:15	10/9/2012 15:15	         100

1. Excluding “weekends”:

Now, how do we take out the “weekend days” out from the “RESPONSE_TIME_IN_DAYS”?

• We must first be aware of every date that falls between the start and end dates.
• Next, we must choose which of those dates is a Saturday or a Sunday.
• Then we add the resulting dates together.

Example: Let's start the process. In the beginning, we will employ a brute force method and produce all "days" beginning after January 1, 1950, for a period of 100 years (about until December, 2050), presuming that our start and finish dates will be within that range (we will refine the solution later). This SQLPerformance.com article demonstrates how to generate n rows dynamically and also explains the use of "MAXDOP 1."

SELECT DATEADD(day,
  (seq-1),
  CONVERT(datetime, '1950/01/01 00:00:00', 120)
  ) AS day_date,
  DATEPART(dw,
  DATEADD(day,
  (seq-1),
  CONVERT(datetime, '1950/01/01 00:00:00', 120)
  )
  ) AS day_of_week
FROM
(
  --100 years
  SELECT TOP (365 * 100) seq = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS a
ORDER BY 1
OPTION (MAXDOP 1)

This query is literally going to generate 100 years’ worth of dates, one per day from 1/1/1950:

DAY_DATE	DAY_OF_WEEK
1-Jan-1950	1
2-Jan-1950	2
3-Jan-1950	3
4-Jan-1950	4
5-Jan-1950	5
6-Jan-1950	6
7-Jan-1950	7
…..more rows inbetween
3-Dec-2049	6
4-Dec-2049	7
5-Dec-2049	1
6-Dec-2049	2

2. Day 1 and 7 make the weekend:

In the table above, DAY_OF_WEEK 7 and 1 correspond to Saturday and Sunday respectively (whose corresponding dates, we want to exclude). More specifically, the numbering is as follows:

Day of Week	Day
1	       Sunday
2	       Monday
3	       Tuesday
4	       Wednesday
5	       Thursday
6	       Friday
7	       Saturday

3. Exclude the weekends:

Example: Below, I have put the above pieces together:

WITH days
AS
(
SELECT DATEADD(day,
 (seq-1),
CONVERT(datetime, '1950/01/01 00:00:00', 120)
 ) AS day_date,
 DATEPART(dw,
 DATEADD(day,
 (seq-1),
CONVERT(datetime, '1950/01/01 00:00:00', 120)
 )
 ) AS day_of_week
FROM
 (
--100 years
 SELECT TOP (365 * 100) seq = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
 FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
 ) AS a
),
response_time
AS
(
 SELECT
 GETDATE() - 600 AS start_date,
 GETDATE() - 500 AS end_date
)
SELECT
 MAX(DATEDIFF(day, start_date, end_date)) AS days_inbetween,
 COUNT(1) business_days_inbetween
FROM
 response_time resp,
 days
WHERE
 --Get only the dates between our start and end dates
 days.day_date >= resp.start_date
 AND days.day_date <= resp.end_date
 --...then exclude Sat and Sun
 AND days.day_of_week NOT IN (7,1)
OPTION (MAXDOP 1)

Result:

4. Exclude holidays too, please!:

Example: That was fairly simple. Different holidays are observed in various nations and areas. Hard-coding the list of holidays into your query is not a smart idea. Now, just tack that on to the end of the query if the holidays were stored in a calendar table (note the highlighted part). There could be only three columns in the Holidays_Table. HolidayDate, a date, a region, and a description of the holiday.

WHERE
--Get only the dates between our start and end dates
  days.day_date >= resp.start_date
  AND days.day_date <= resp.end_date
--...then exclude Sat and Sun
  AND days.day_of_week NOT IN (7,1)
--...then exclude week-day holidays
  AND NOT EXISTS
  (
   SELECT holiday
   FROM holidays_table ht
   WHERE ht.holiday = days.day_date
--Optionally include Region!
   )

5. Need a more efficient solution?

Example: While this works, it can certainly be tweaked a bit more. We do not need to generate 100 years’ worth of dates – we need to generate exactly the dates in between the start date and end date.

WITH days
AS
(
 SELECT
  DATEADD(day, (seq-1), @Begin_Date) AS day_date,
  DATEPART(dw, DATEADD(day, (seq-1), @Begin_Date)) day_of_week
FROM
(
--Only the x days between begin and end dates
  SELECT TOP (@Days_Between) seq
  = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
  FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
 ) a
)

Where the variables could be (for example)

DECLARE @Begin_Date DATETIME
DECLARE @End_Date DATETIME
DECLARE @Days_Between INT
 
SET @Begin_Dat'e = CONVERT(datetime, '1971/01/01 00:00:00', 120)
SET @End_Date  = CONVERT(datetime, '1981/01/01 00:00:00', 120)
SET @Days_Between = DATEDIFF(day, @Begin_Date, @End_Date)

The day generator SQL has been simplified and made more effective as well, as you can see by paying attention to the highlighted variable substitutions.

The DATE_ADD method increases a DATE or DATETIME value by an interval.

Using MySQL's DATE_ADD() function, you can add a specific time or date interval to a given date and then get back the original date.

Syntax:

The following illustrates the syntax of the DATE_ADD function:

DATE_ADD(start_date, INTERVAL expr unit);

The DATE_ADD function takes two arguments:

• start_date is a starting DATE or DATETIME value
• The interval value, INTERVAL expr unit, is to be added to the starting date value.

The DATE_ADD function may return a DATETIME value or a string, depending on the arguments:

DATETIME if the first argument is a DATETIME value or if the interval value has time element such as hour, minute or second, etc.

Example 1: Add 1 second to 1999-12-31 23:59:59:

SELECT 
  DATE_ADD('1999-12-31 23:59:59',
  INTERVAL 1 SECOND) result;

Output:

Example 2: Getting a new date of “2020-11-22” after the addition of 3 years to the specified date “2017-11-22”.

SELECT DATE_ADD("2017-11-22", INTERVAL 3 YEAR);

Output:

When used in a numeric environment, the NOW() function returns a number. You can use it to perform computations such as now plus one hour, now minus one hour, and now minus one day.

Example 1: You must deduct one day from the current date in order to obtain yesterday's. To obtain today's date (whose type is datetime), use GETDATE() and convert it to date. The SQL Server DATEADD() function allows you to add or subtract any number of days. Datepart, number, and date are the three inputs required by the DATEADD() function.

DATEADD(DAY, -30, GETDATE())

Example 2: The following statement returns the current date and time, now minus 1 day :

-- mysql now minus 1 day
SELECT (NOW() - INTERVAL 1 DAY) 'NOW - 1 day',
        NOW(),

Example: In SQL Server, the DATEADD() function can be used to subtract weeks from a date and time. Week, wk, or ww are all valid values for the first parameter of the DATEADD() function; all will produce the same output. The example below demonstrates how to use SQL Server to subtract two weeks from the current date and time:

SELECT GETDATE() 'Today',
   DATEADD(week,-3,GETDATE()) 'Today - 3 Weeks'
SELECT GETDATE() 'Today',
   DATEADD(wk,-3,GETDATE()) 'Today - 3 weeks'
SELECT GETDATE() 'Today',
   DATEADD(ww,-3,GETDATE()) 'Today - 3 Weeks'

Output:

Today                          Today-3 weeks
2015-06-13 00:33:26.747    2015-05-25 00:33:26.747
2015-06-13 00:33:26.747    2015-05-25 00:33:26.747
2015-06-13 00:33:26.747    2015-05-25 00:33:26.747

Syntax: Here is the syntax to subtract 30 days from current datetime.

DATE_SUB(NOW(),INTERVAL 30 DAY); 

The above syntax calculates the current datetime first and in the next step, subtracts 30 days.

Example 1: You can subtract 7 from the current date with this:

WHERE datex BETWEEN DATEADD(day, -7, GETDATE()) AND GETDATE() 

I prefer “select (sysdate – interval’1' day) from dual” With the plain “sysdate-1” it get’s more complicated in the case of hours, weeks etc. but with this approach it comes along – terix2k11 Mar 30’17 at 14:41.

WHERE ss.DATE DATEADD (“day”,-30, ss.date) 

With Build TABLE, you can create a temptable (or regulartable with no prefix). Then, you may fill it with data using INSERT SELECT. With DECLARE, a table variable can be made. Then, you may fill it with data using INSERT SELECT. I need to change the current date in DB2 mikrom by one day (Programmer) 23 Mar 12 04:34 Although CURRENT_DATE returns results in DATE format, it is also possible to save dates in numeric formats with 8 digits, such as DECIMAL(8,0), which returns YYYYMMDD (often used in COBOL programs). 7 for, 7 against. When doing "purchase_date(sysdate-30)," keep in mind that "sysdate" represents the current date, hour, minute, and second.

Therefore, "sysdate-30" actually means "30 days ago at this exact hour," rather than "30 days ago." If the hours, minutes, and seconds on your purchase dates are zero, you should do: How Do I Take Days Out of a DateTime in SQL Server? To subtract days from a date and time in SQL Server, use the DATEADD() method as shown below. Day, dd, or d can all be used as the first parameter value in the DATEADD() function to have the same outcome.

Example 2: Below example shows how we can subtract two days from Current DateTime in Sql Server:

SELECT DATEADD(day,-1,’1 16:25:’) 

The above will give you 1 16:25:. It takes you back exactly one day and works on any standard date-time or date format.

Example 3: Try running this command and see if it gives you what you are looking for:

SELECT DATEADD(day,-1,CreatedDate)

Example 4: Add 30 days to a date:

SELECT DATEADD(DD,30,Date)

Example 5: Add 3 hours to a date:

SELECT DATEADD(HOUR,-3,Date)

Example 6: Subtract 90 minutes from date:

SELECT DATEADD(MINUTE,-90,Date)

The now() gives the current date time. The method to be used for this is DATE_SUB() from MySQL.

In SQL Server, the DATEADD() function can be used to subtract months from a date and time as seen below. Month, mm, or m are all acceptable first parameter values for the DATEADD() function; all will provide the same output.

Syntax:

SELECT ADD_MONTHS('YYYY-MM-DD' , -n)

Example 1: Below example shows how we can Subtract two months from Current DateTime in Sql Server:

SELECT GETDATE() 'Today', 
   DATEADD(month,-3,GETDATE()) 'Today - 3 Months'
SELECT GETDATE() 'Today',
   DATEADD(mm,-3,GETDATE()) 'Today - 3 Months'
SELECT GETDATE() 'Today',
   DATEADD(m,-3,GETDATE()) 'Today - 3 Months'

Result:

Today                           Today -3 Month
2014-06-13 00:35:14.707        2014-03-13 00:35:14.707 
2014-06-13 00:35:14.707        2014-03-13 00:35:14.707      
2014-06-13 00:35:14.707        2014-03-13 00:35:14.707

Example 2: Subtracting 2 months:

SELECT ADD_MONTHS ('2000-08-15' , -2);

Result:

Example 3: Subtracting 1 month:

SELECT ADD_MONTHS ('2000-09-30' , -1);

Result:

We can subtract years from a date and time in SQL Server by using the DATEADD() method as shown below. Year, yyyy, or yy, as the first input value, will all yield the same result when used with the DATEADD() function.

Example: Below example shows how we can Subtract two Years from Current DateTime in Sql Server:

SELECT GETDATE() 'Today',
   DATEADD(year,-2,GETDATE()) 'Today - 2 Years'
SELECT GETDATE() 'Today',
   DATEADD(yyyy,-2,GETDATE()) 'Today - 2 Years'
SELECT GETDATE() 'Today',
   DATEADD(yy,-2,GETDATE()) 'Today - 2 Years'

Result:

Today                        Today-2 Years
2015-06-13 00:39:17.167    2013-06-13 00:39:17.167
2015-06-13 00:39:17.167    2013-06-13 00:39:17.167
2015-06-13 00:39:17.167    2013-06-13 00:39:17.167

The DATEADD method will reduce that if you give a negative integer.

Example 1: In this Sql Server Dateadd example, we use the Negative integer as a second argument:

DECLARE @DateAdd datetime2 = '2015-01-01 14:24:04.1234567'
SELECT 'YEAR' AS [DatePart], DATEADD(year, -1, @DateAdd) AS [New Date] 
UNION ALL
SELECT 'QUARTER', DATEADD(quarter, -1, @DateAdd) 
UNION ALL
SELECT 'MONTH', DATEADD(month, -1, @DateAdd) 
UNION ALL
SELECT 'DAYOFYEAR', DATEADD(dayofyear, -1, @DateAdd) 
UNION ALL
SELECT 'DAY', DATEADD(day, -1, @DateAdd) 
UNION ALL
SELECT 'WEEK', DATEADD(week, -1, @DateAdd) 
UNION ALL
SELECT 'WEEKDAY', DATEADD(weekday, -1, @DateAdd) 
UNION ALL
SELECT 'HOUR', DATEADD (hour, -1, @DateAdd) 
UNION ALL
SELECT 'MINUTE', DATEADD(minute, -1, @DateAdd) 
UNION ALL
SELECT 'SECOND', DATEADD(second, -14, @DateAdd)

The function is subtracting a year from the specified date despite our request to add a year. This is so that subtractions can be executed (year, -1, @DateAdd).

The SQL Server Dateadd function is deleting one month from the specified date even though we asked it to add one. This is so that subtractions can be made (month, -1, @DateAdd).

Example 2: As we previously saw, DATEADD may be used for both addition and subtraction, making it simple to compute values both forwards and backwards. Let's say we need to figure out what happened 100 days ago. If we were to begin with today, it would appear as follows:

DECLARE @dt DATETIME2 = SYSUTCDATETIME();
SELECT @dt AS [TimeNow], DATEADD(DAY, -100, @dt) AS [TimeThen];

Notice the use of the negative sign in the number portion.

Result:

Example 3: You can use negative numbers to subtract from the date:

SELECT DATEADD(year, -10, '2020-10-03') AS 'Earlier Date';

Result:

And of course, you can format this using any of the methods previously mentioned.

You can add a sum to a date or datetime expression using the DATE_ADD() method. Included in this is the addition of time components like hours, minutes, seconds, etc.

To add seconds to a DateTime in SQL Server, use the DATEADD() method as shown below. The first parameter value for the DATEADD() function can be second, ss, or s; all will provide the same outcome.

Example 1: Below example shows how we can add two seconds to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
   DATEADD(second,2,GETDATE()) 'Now + 2 Seconds'
SELECT GETDATE() 'Now',
   DATEADD(ss,2,GETDATE()) 'Now + 2 Seconds'
SELECT GETDATE() 'Now',
   DATEADD(s,2,GETDATE()) 'Now + 2 Seconds'

Result:

Now                              Now +2 seconds 
2014-07-06 01:41:29.713        2014-07-06 01:41:32.713
2014-07-06 01:41:29.713        2014-07-06 01:41:32.713
2014-07-06 01:41:29.713        2014-07-06 01:41:32.713

Example 2:

SELECT DATE_ADD('2021-05-01 10:00:00', INTERVAL 35 SECOND);

Result:

I increased the datetime expression in this instance by 35 seconds. No matter how many seconds I add, the SECOND keyword still only has one occurrence.

Example 3: Here’s what happens if I provide just the date:

SELECT DATE_ADD('2021-05-01', INTERVAL 35 SECOND);

Result:

The time part is appended to the result, and it is assumed that the initial time was 00:00:00.

Example 4: When adding 60 seconds, we can alternatively use the MINUTE keyword if we prefer:

SELECT DATE_ADD('2021-05-01 10:00:00', INTERVAL 1 MINUTE);

Result:

It is evident that this method works with 120, 180, and so forth. Simply use the corresponding number of minutes.

Example 5: To add several units, you can alternatively use a composite date/time unit. For instance, you could follow these steps to add 1 minute and 30 seconds:

SELECT 
DATE_ADD('2021-05-01 10:00:00', INTERVAL '1:30' MINUTE_SECOND);

DATE_ADD('2021-05-01 10:00:00', INTERVAL '1:30' MINUTE_SECOND) 

Result:

To subtract days from a date and time in SQL Server, use the DATEADD() method as shown below. Day, dd, or d can all be used as the first parameter value in the DATEADD() function to have the same outcome.

Example 1: Below example shows how we can subtract two days from Current DateTime in Sql Server:

SELECT GETDATE() 'Today', 
  DATEADD(day,-2,GETDATE()) 'Today - 2 Days'
SELECT GETDATE() 'Today', 
  DATEADD(dd,-2,GETDATE()) 'Today - 2 Days'
SELECT GETDATE() 'Today', 
  DATEADD(d,-2,GETDATE()) 'Today - 2 Days'

Result:

Today                          Today-2 Days
2015-06-13 00:29:34.250       2015-06-11 00:29:34.250       
2015-06-13 00:29:34.250       2015-06-11 00:29:34.250  
2015-06-13 00:29:34.250       2015-06-11 00:29:34.250  

Example 2: As a substitute, we may use Sql Server to Subtract Days from Datetime as shown below. In the instance below, we are taking two days out of DateTime.

SELECT GETDATE() 'Today', GETDATE() - 2 'Today - 2 Days'

Result:

Today                           Today-2 Days
2015-06-13 00:29:34.250       2015-06-11 00:29:34.250 

Example 3: Our database has a table named Computer with data in the columns Id, Name, and PurchaseDate.

Id	Name		PurchaseDate
1	Sony GX1000	2019-01-20
2	Samsung LX2000	2019-04-15
3	Dell K80	2019-08-30

Let’s get the name of each computer and the date 30 days before its purchase date.

We will use the DATEADD() function to subtract a given number of days from a date.

SELECT Name,
  DATEADD(day, -30, PurchaseDate)
  AS BeforePurchaseDate;
  FROM Computer;

Result:

Name	BeforePurchaseDate
Sony GX1000	2018-12-21
Samsung LX2000	2019-03-16
Dell K80	2019-07-31

Discussion:

• Use the DATEADD() method in SQL Server if you want to subtract dates or times. Three reasons are needed. In our example, we supply the day unit as the first input for the date/time unit.
• The date or time unit value comes next. In our example, this is -30 because we are deducting the current date by 30 days. Keep in mind that the negative sign indicates subtraction; without it, you are adding to the specified date.
• The date we're working with is the last argument; it might be a date/time/datetime column or any expression that yields a date or time. We utilise the date field PurchaseDate in our example.
• The function returns a changed date. In our example, the query for the computer named 'Dell K80' returns a new date in the BeforePurchaseDate column. The original date '2019-08-30' is changed to the date from 30 days back: '2018-07-31'

In SQL Server, we can subtract quarters from a datetime by using the DATEADD() method as seen below. The first parameter value for the DATEADD() function can be quarter, qq, or q; all will provide the same outcome.

The quarter of the year in which the date falls is represented by an integer between 1 and 4 that is returned by the QUARTER function.

Example 1: Below example shows how we can Subtract two Quarters from Current DateTime in Sql Server:

SELECT GETDATE() 'Today',
   DATEADD(quarter,-2,GETDATE()) 'Today - 2 Quarters'
SELECT GETDATE() 'Today',
   DATEADD(qq,-2,GETDATE()) 'Today - 2 Quarters'
SELECT GETDATE() 'Today',
   DATEADD(q,-2,GETDATE()) 'Today - 2 Quarters'

Result:

Today                          Today-2 Quarters
2015-06-13 00:37:53.867       2014-12-13 00:37:53.867
2015-06-13 00:37:53.867       2014-12-13 00:37:53.867
2015-06-13 00:37:53.867       2014-12-13 00:37:53.867

Example 2: For example, any dates in January, February, or March return the integer 1.

SELECT GETDATE() 'Current_Date', 
    DATEADD(QUARTER,1,GETDATE()) 'Current_Date + 1 QUARTER'
SELECT GETDATE() 'Current_Date', 
    DATEADD(QQ,-3,GETDATE()) 'Current_Date - 3 QUARTER'
SELECT GETDATE() 'Current_Date',
    DATEADD(Q,-4,GETDATE()) 'Current_Date - 4 QUARTER'

Datetime datatype stores date and time part.

Example 1: If you have only date value, You can add time part to a datetime variable using direct literal value and + operator.

DECLARE @date DATETIME
SET @date='2010-10-01'
SET @date=@date+'15:00:00'
SELECT @date AS DATETIME

Result:

But it is not possible to add it using a time variable:

DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+@time
SELECT @date

The error is:

Example 2: Convert time datatype into datetime and add:

DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+CAST(@time AS DATETIME)
SELECT @date AS DATETIME

Result:

Use the DATEADD() function if you want to add months or years to the current date.

Syntax:-

DATEADD( datepart, number, date)

Where,  

• datepart = year, quarter, month, day… (Check BOL for full list)
• number = is a integer values that is to be added to the “datepart” of date.
• date = date

Example 1: You would like to display yesterday's date (without time) in an Oracle database.

SELECT TO_DATE(current_date - 1) AS yesterday_date
FROM dual

Assuming today is 2020-09-24, the result is:

Discussion: To get yesterday's date, you need to subtract one day from today. Use current_date to get today's date. In Oracle, you can subtract any number of days simply by subtracting that number from the current date. Here, since you need to subtract one day, you use current_date - 1. Then you use the TO_DATE() function to cast the result to the column type date.

Example 2: You can go back by any number of days you want very easily, e.g., by seven days.

SELECT TO_DATE(current_date - 7) AS date_week_ago FROM dual;

You can also calculate a date in the future. For example, to get tomorrow's date, you add one to current_date:

SELECT TO_DATE(current_date + 1) AS tomorrow_date FROM dual

Example 3: To add or subtract days from a date, you can simply use:

GETDATE() + 10 AS '10 Days Later',

GETDATE() – 10 AS '10 Days Earlier'

Result:

Today                   10 Days Later           10 Days Earlier
———————–	         ———————–		 ———————–
2011-05-20 21:04:24.527 2011-05-30 21:04:24.527 2011-05-10 21:04:24.527

Example 4: It can be used as:

SELECT      GETDATE(), 'Today'
UNION ALL
SELECT      DATEADD(DAY,  10, GETDATE()), '10 Days Later'
UNION ALL
SELECT      DATEADD(DAY, –10, GETDATE()), '10 Days Earlier'
UNION ALL
SELECT      DATEADD(MONTH,  1, GETDATE()), 'Next Month'
UNION ALL
SELECT      DATEADD(MONTH, –1, GETDATE()), 'Previous Month'
UNION ALL
SELECT      DATEADD(YEAR,  1, GETDATE()), 'Next Year'
UNION ALL
SELECT      DATEADD(YEAR, –1, GETDATE()), 'Previous Year'

Output:

2011-05-20 21:11:42.390 Today
2011-05-30 21:11:42.390 10 Days Later
2011-05-10 21:11:42.390 10 Days Earlier
2011-06-20 21:11:42.390 Next Month
2011-04-20 21:11:42.390 Previous Month
2012-05-20 21:11:42.390 Next Year
2010-05-20 21:11:42.390 Previous Year

A date can be added to or subtracted using the DATEADD function.

This function modifies a specified datepart of an input date value by adding a given number value (as a signed integer), and then it returns the updated value.

Syntax:

DATEADD (datepart, number, date)

• Any portion of the date, such as the day, month, year, weekday, hour, etc., is referred to as a datepart.
• When adding or subtracting a datepart (day, month, year, etc.), number is the number of that datepart.
• Date is a provided date that requires the DATEADD function to be appended to or subtracted.

Example 1: Getting Next Year Date

Let's establish another date (the registration date) that will be added or deleted using the DATEADD method in accordance with the needs.

By adding 1 to the Year datepart, the following year's date can be derived.

Simply put DatePart Year followed by 1 followed by Registration Date (@RegistrationDate) in the DATEADD method to obtain the following year from the registration date:

-- Define Registration Date
DECLARE @RegDate DATETIME2='2018-07-10'

-- Getting Next Year from registratoin date
SELECT DATEADD(YEAR,1,@RegDate) as NextYear_RegDate

Example 2: Getting Next Month Date

The MONTH datepart, the number of months we wish to add, and the given date—in this case, the registration date (RegDate)—must be passed to the DATEADD function in order to obtain the next month's date.

-- Define Registration Date
DECLARE @RegDate DATETIME2='2018-07-10'
SELECT @RegDate AS RegDate -- Show Registration Date

-- Getting Next MONTH from the registratoin date
SELECT DATEADD(MONTH,1,@RegDate) as NextMonth_RegDate

Example 3: Getting Next Day Date

Since the next day adds another day to the registration date, if the course starts on the next (following) day of registration, we must pass DAY with 1 as shown below:

-- Define Registration Date
DECLARE @RegDate DATETIME2='2018-07-10'
SELECT @RegDate AS RegDate -- Show Registration Date

-- Getting Next DAY from registratoin date
SELECT DATEADD(DAY,1,@RegDate) as NextDAY_RegDate

Example 4: Setting up Daily Sales Report Date:

Let's now concentrate on a moderately complex scenario that is frequently utilised in the creation of daily financial reports.

As we explained at the beginning of this post, the most recent complete day is yesterday, therefore if we want to construct a daily sales report, it should include data from yesterday.

In order to make it simpler for the report to include the entire day, we also need to change the Date Time information from yesterday into a Date only value.

We must add "-1 day" to the current date in order to obtain yesterday's date based on today's date:

-- Define Current Date
DECLARE @CurrentDate DATETIME2=GETDATE()
SELECT @CurrentDate AS CurrentDate -- Show Registration Date

-- Getting Yesterday Date and Time from current date
SELECT DATEADD(DAY,-1,@CurrentDate) as YesterdayDateTime_CurrentDate

-- Converting Yesterday DateTime into Date only
SELECT CAST(DATEADD(DAY,-1,@CurrentDate) AS DATE)as YesterdayDateOnly_CurrentDate

Example 5: Let us see how we can combine and date and time in SQL Server.

DECLARE @dt DATE = '2020-07-12'
DECLARE @tm TIME = '07:01:01.000'
SELECT CAST(@dt AS DATETIME) + CAST(@tm AS DATETIME) AS [CombineDateTime]

Method 2: Using DATEADD

DECLARE @dt DATE = '2020-07-12'
DECLARE @tm TIME = '07:01:01.000'
SELECT DATEADD(DAY,DATEDIFF(DAY, 0, @dt),CAST(@tm AS DATETIME)) AS [CombineDateTime]

There is also one more method I often see people using in the real world is the function CONCAT.

Example 6: I just discovered that a table containing two columns, one of which had a DATE datatype and the other a TIME datatype, required to be combined into a single DATETIME result column. I won't delve into the reasoning behind why the DATE and TIME were split in this instance, but suffice it to say that it made sense at the time.

There are numerous approaches to doing this, just like there are for the majority of date operations in T-SQL. Let's examine the first idea that entered my head.

DECLARE @MyDate   DATE = '2015-08-27'
,@MyTime      TIME = '15:33:21.057';
SELECT MyDateTime1=CAST(@MyDate AS DATETIME) + CAST(@MyTime AS DATETIME);

This works because in T-SQL it is possible to add (or subtract) two DATETIME values, and you’ll find that it produces exactly the desired result, which is: 2015-08-27 15:33:21.057.

Example 7: I chose to look into some other options because I was aware from prior experience that the first query type that comes to mind isn't necessarily the quickest from a performance standpoint.

Here’s another way that will work:

SELECT MyDateTime2=DATEADD(millisecond
   ,DATEDIFF(millisecond, 0, @MyTime)
   ,CAST(@MyDate AS DATETIME));

Example 8: You may use those links to access the Microsoft Books-on-Line reference for the DATEADD and DATEDIFF functions if you are unfamiliar with them, or you can view some instances in one of my earlier blogs, Manipulating Dates and Times with T-SQL.

SELECT MyDateTime3=DATEADD(day
      ,DATEDIFF(day, 0, @MyDate)
      ,CAST(@MyTime AS DATETIME));

Finally, we’ll get a little creative and use a VARCHAR intermediate result to come up with this:

SELECT MyDateTime4=CAST(
    CAST(@MyDate AS VARCHAR(10)) + ' ' + CAST(@MyTime AS VARCHAR(12)) 
    AS DATETIME);

Example 9: So now we have some choices, but which do we use:

Although some could claim that the third one is simpler to understand, the first one is the shortest in terms of keystrokes and arguably the simplest to recognise.

But in my opinion, performance always matters—I've said it before, and I'm sure to say it again. Even if this tiny piece of code is a component of a much larger T-SQL query, getting the fastest performance out of that complex query implies getting the fastest performance out of each of its tiniest components.

So it only seems sense that we'll use the One Million Row Test Harness, one of our favourite performance tools. It is almost embarrassingly simple to make one of these for this situation; the embarrassing thing, of course, is how few people might think to do so. This is it.

WITH Tally (n) AS
(
    SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
SELECT MyDate = DATEADD(day, 1+ABS(CHECKSUM(NEWID()))%10000, CAST('1900-01-01' AS DATE))
    ,MyTime  = DATEADD(millisecond, 1+ABS(CHECKSUM(NEWID()))%86400000, CAST('00:00' AS TIME))
INTO #Temp
FROM Tally;
DECLARE @MyDateTime DATETIME;
SET STATISTICS TIME ON;
SELECT @MyDateTime=CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME)
FROM #Temp;
SET STATISTICS TIME OFF;
SET STATISTICS TIME ON;
SELECT @MyDateTime=DATEADD(millisecond
   ,DATEDIFF(millisecond, 0, MyTime)
   ,CAST(MyDate AS DATETIME))
FROM #Temp;
SET STATISTICS TIME OFF;
SET STATISTICS TIME ON;
SELECT @MyDateTime=DATEADD(day
   ,DATEDIFF(day, 0, MyDate)
   ,CAST(MyTime AS DATETIME))
FROM #Temp;
SET STATISTICS TIME OFF;
SET STATISTICS TIME ON;
SELECT @MyDateTime=CAST(
   CAST(MyDate AS VARCHAR(10)) + ' ' + CAST(MyTime AS VARCHAR(12)) 
   AS DATETIME)
FROM #Temp;
SET STATISTICS TIME OFF;
GO
DROP TABLE #Temp;

Short and sweet! You gotta love that in a performance test harness.

To avoid unavoidable delays in presenting our one million rows of test data to the SSMS Results pane, which can lead to some variation in the timing results, we've moved the results in each example to a local variable.

We selected the exact in-line Tally Table that we're utilising since, thanks to its small character count, it's probably one of the quickest to enter. Naturally, any tally table with a million rows would do.

By simply commenting out the INTO clause of the query and displaying the results on the screen, the SELECT INTO construct also makes it rather simple to verify that we are indeed inserting DATE and TIME datatypes into our #Temp table.

Example 10: By now I’ve done enough talking, so I’ll cut to the chase. That is, which of our four methods of combining a DATE and a TIME into a DATETIME ends up being the fastest:

SQL Server Execution Times:

CPU time = 171 ms,  elapsed time = 174 ms.

SQL Server Execution Times:

CPU time = 234 ms,  elapsed time = 233 ms.

SQL Server Execution Times:

CPU time = 234 ms,  elapsed time = 233 ms.

SQL Server Execution Times:

CPU time = 1123 ms,  elapsed time = 1113 ms.

We have it there! As it turns out, my initial assumption outperforms the other approaches fairly handily, especially that dreadful one that makes use of the VARCHAR intermediate. It is crucial to use your imagination and explore what your SQL-brain can invent for situations like this because that won't always be the case.