Как прибавить месяц к дате в sql
Table 9.30 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 9.29 illustrates the behaviors of the basic arithmetic operators ( + , * , etc.). For formatting functions, refer to Section 9.8. You should be familiar with the background information on date/time data types from Section 8.5.
All the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone , and one that takes time without time zone or timestamp without time zone . For brevity, these variants are not shown separately. Also, the + and * operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair.
Table 9.29. Date/Time Operators
| Operator | Example | Result |
|---|---|---|
| + | date ‘2001-09-28’ + integer ‘7’ | date ‘2001-10-05’ |
| + | date ‘2001-09-28’ + interval ‘1 hour’ | timestamp ‘2001-09-28 01:00:00’ |
| + | date ‘2001-09-28′ + time ’03:00’ | timestamp ‘2001-09-28 03:00:00’ |
| + | interval ‘1 day’ + interval ‘1 hour’ | interval ‘1 day 01:00:00’ |
| + | timestamp ‘2001-09-28 01:00′ + interval ’23 hours’ | timestamp ‘2001-09-29 00:00:00’ |
| + | time ’01:00′ + interval ‘3 hours’ | time ’04:00:00′ |
| — | — interval ’23 hours’ | interval ‘-23:00:00’ |
| — | date ‘2001-10-01’ — date ‘2001-09-28’ | integer ‘3’ (days) |
| — | date ‘2001-10-01’ — integer ‘7’ | date ‘2001-09-24’ |
| — | date ‘2001-09-28’ — interval ‘1 hour’ | timestamp ‘2001-09-27 23:00:00’ |
| — | time ’05:00′ — time ’03:00′ | interval ’02:00:00′ |
| — | time ’05:00′ — interval ‘2 hours’ | time ’03:00:00′ |
| — | timestamp ‘2001-09-28 23:00′ — interval ’23 hours’ | timestamp ‘2001-09-28 00:00:00’ |
| — | interval ‘1 day’ — interval ‘1 hour’ | interval ‘1 day -01:00:00’ |
| — | timestamp ‘2001-09-29 03:00’ — timestamp ‘2001-09-27 12:00’ | interval ‘1 day 15:00:00’ |
| * | 900 * interval ‘1 second’ | interval ’00:15:00′ |
| * | 21 * interval ‘1 day’ | interval ’21 days’ |
| * | double precision ‘3.5’ * interval ‘1 hour’ | interval ’03:30:00′ |
| / | interval ‘1 hour’ / double precision ‘1.5’ | interval ’00:40:00′ |
Table 9.30. Date/Time Functions
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| age( timestamp , timestamp ) | interval | Subtract arguments, producing a “ symbolic ” result that uses years and months, rather than just days | age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) | 43 years 9 mons 27 days |
| age( timestamp ) | interval | Subtract from current_date (at midnight) | age(timestamp ‘1957-06-13’) | 43 years 8 mons 3 days |
| clock_timestamp() | timestamp with time zone | Current date and time (changes during statement execution); see Section 9.9.4 | ||
| current_date | date | Current date; see Section 9.9.4 | ||
| current_time | time with time zone | Current time of day; see Section 9.9.4 | ||
| current_timestamp | timestamp with time zone | Current date and time (start of current transaction); see Section 9.9.4 | ||
| date_part( text , timestamp ) | double precision | Get subfield (equivalent to extract ); see Section 9.9.1 | date_part(‘hour’, timestamp ‘2001-02-16 20:38:40’) | 20 |
| date_part( text , interval ) | double precision | Get subfield (equivalent to extract ); see Section 9.9.1 | date_part(‘month’, interval ‘2 years 3 months’) | 3 |
| date_trunc( text , timestamp ) | timestamp | Truncate to specified precision; see also Section 9.9.2 | date_trunc(‘hour’, timestamp ‘2001-02-16 20:38:40’) | 2001-02-16 20:00:00 |
| date_trunc( text , interval ) | interval | Truncate to specified precision; see also Section 9.9.2 | date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’) | 2 days 03:00:00 |
| extract ( field from timestamp ) | double precision | Get subfield; see Section 9.9.1 | extract(hour from timestamp ‘2001-02-16 20:38:40’) | 20 |
| extract ( field from interval ) | double precision | Get subfield; see Section 9.9.1 | extract(month from interval ‘2 years 3 months’) | 3 |
| isfinite( date ) | boolean | Test for finite date (not +/-infinity) | isfinite(date ‘2001-02-16’) | true |
| isfinite( timestamp ) | boolean | Test for finite time stamp (not +/-infinity) | isfinite(timestamp ‘2001-02-16 21:28:30’) | true |
| isfinite( interval ) | boolean | Test for finite interval | isfinite(interval ‘4 hours’) | true |
| justify_days( interval ) | interval | Adjust interval so 30-day time periods are represented as months | justify_days(interval ’35 days’) | 1 mon 5 days |
| justify_hours( interval ) | interval | Adjust interval so 24-hour time periods are represented as days | justify_hours(interval ’27 hours’) | 1 day 03:00:00 |
| justify_interval( interval ) | interval | Adjust interval using justify_days and justify_hours , with additional sign adjustments | justify_interval(interval ‘1 mon -1 hour’) | 29 days 23:00:00 |
| localtime | time | Current time of day; see Section 9.9.4 | ||
| localtimestamp | timestamp | Current date and time (start of current transaction); see Section 9.9.4 | ||
| make_date( year int , month int , day int ) | date | Create date from year, month and day fields | make_date(2013, 7, 15) | 2013-07-15 |
| make_interval( years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) | interval | Create interval from years, months, weeks, days, hours, minutes and seconds fields | make_interval(days => 10) | 10 days |
| make_time( hour int , min int , sec double precision ) | time | Create time from hour, minute and seconds fields | make_time(8, 15, 23.5) | 08:15:23.5 |
| make_timestamp( year int , month int , day int , hour int , min int , sec double precision ) | timestamp | Create timestamp from year, month, day, hour, minute and seconds fields | make_timestamp(2013, 7, 15, 8, 15, 23.5) | 2013-07-15 08:15:23.5 |
| make_timestamptz( year int , month int , day int , hour int , min int , sec double precision , [ timezone text ]) | timestamp with time zone | Create timestamp with time zone from year, month, day, hour, minute and seconds fields; if timezone is not specified, the current time zone is used | make_timestamptz(2013, 7, 15, 8, 15, 23.5) | 2013-07-15 08:15:23.5+01 |
| now() | timestamp with time zone | Current date and time (start of current transaction); see Section 9.9.4 | ||
| statement_timestamp() | timestamp with time zone | Current date and time (start of current statement); see Section 9.9.4 | ||
| timeofday() | text | Current date and time (like clock_timestamp , but as a text string); see Section 9.9.4 | ||
| transaction_timestamp() | timestamp with time zone | Current date and time (start of current transaction); see Section 9.9.4 | ||
| to_timestamp( double precision ) | timestamp with time zone | Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp | to_timestamp(1284352323) | 2010-09-13 04:32:03+00 |
In addition to these functions, the SQL OVERLAPS operator is supported:
This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval. When a pair of values is provided, either the start or the end can be written first; OVERLAPS automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval start <= time < end , unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.
When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances or decrements the date of the timestamp with time zone by the indicated number of days. Across daylight saving time changes (when the session time zone is set to a time zone that recognizes DST), this means interval ‘1 day’ does not necessarily equal interval ’24 hours’ . For example, with the session time zone set to CST7CDT , timestamp with time zone ‘2005-04-02 12:00-07’ + interval ‘1 day’ will produce timestamp with time zone ‘2005-04-03 12:00-06′ , while adding interval ’24 hours’ to the same initial timestamp with time zone produces timestamp with time zone ‘2005-04-03 13:00-06’ , as there is a change in daylight saving time at 2005-04-03 02:00 in time zone CST7CDT .
Note there can be ambiguity in the months field returned by age because different months have different numbers of days. PostgreSQL ‘s approach uses the month from the earlier of the two dates when calculating partial months. For example, age(‘2004-06-01’, ‘2004-04-30’) uses April to yield 1 mon 1 day , while using May would yield 1 mon 2 days because May has 31 days, while April has only 30.
Subtraction of dates and timestamps can also be complex. One conceptually simple way to perform subtraction is to convert each value to a number of seconds using EXTRACT(EPOCH FROM . ) , then subtract the results; this produces the number of seconds between the two values. This will adjust for the number of days in each month, timezone changes, and daylight saving time adjustments. Subtraction of date or timestamp values with the “ — ” operator returns the number of days (24-hours) and hours/minutes/seconds between the values, making the same adjustments. The age function returns years, months, days, and hours/minutes/seconds, performing field-by-field subtraction and then adjusting for negative field values. The following queries illustrate the differences in these approaches. The sample results were produced with timezone = ‘US/Eastern’ ; there is a daylight saving time change between the two dates used:
9.9.1. EXTRACT , date_part
The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp , time , or interval . (Expressions of type date are cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns values of type double precision . The following are valid field names:
The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 century to 1 century. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican.
For timestamp values, the day (of the month) field (1 — 31) ; for interval values, the number of days
The year field divided by 10
The day of the week as Sunday ( 0 ) to Saturday ( 6 )
Note that extract ‘s day of the week numbering differs from that of the to_char(. ‘D’) function.
The day of the year (1 — 365/366)
For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval
You can convert an epoch value back to a time stamp with to_timestamp :
The hour field (0 — 23)
The day of the week as Monday ( 1 ) to Sunday ( 7 )
This is identical to dow except for Sunday. This matches the ISO 8601 day of the week numbering.
This field is not available in PostgreSQL releases prior to 8.3.
The seconds field, including fractional parts, multiplied by 1 000 000; note that this includes full seconds
Years in the 1900s are in the second millennium. The third millennium started January 1, 2001.
The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.
The minutes field (0 — 59)
For timestamp values, the number of the month within the year (1 — 12) ; for interval values, the number of months, modulo 12 (0 — 11)
The quarter of the year (1 — 4) that the date is in
The seconds field, including fractional parts (0 — 59 [7] )
The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC. (Technically, PostgreSQL does not use UTC because leap seconds are not handled.)
The hour component of the time zone offset
The minute component of the time zone offset
In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31 is part of the first week of 2013. It’s recommended to use the isoyear field together with week to get consistent results.
The year field. Keep in mind there is no 0 AD , so subtracting BC years from AD years should be done with care.
When the input value is +/-Infinity, extract returns +/-Infinity for monotonically-increasing fields ( epoch , julian , year , isoyear , decade , century , and millennium ). For other fields, NULL is returned. PostgreSQL versions before 9.6 returned zero for all cases of infinite input.
The extract function is primarily intended for computational processing. For formatting date/time values for display, see Section 9.8.
The date_part function is modeled on the traditional Ingres equivalent to the SQL -standard function extract :
Note that here the field parameter needs to be a string value, not a name. The valid field names for date_part are the same as for extract .
9.9.2. date_trunc
The function date_trunc is conceptually similar to the trunc function for numbers.
source is a value expression of type timestamp or interval . (Values of type date and time are cast automatically to timestamp or interval , respectively.) field selects to which precision to truncate the input value. The return value is of type timestamp or interval with all fields that are less significant than the selected one set to zero (or one, for day and month).
Valid values for field are:
| microseconds |
| milliseconds |
| second |
| minute |
| hour |
| day |
| week |
| month |
| quarter |
| year |
| decade |
| century |
| millennium |
9.9.3. AT TIME ZONE
The AT TIME ZONE converts time stamp without time zone to/from time stamp with time zone , and time values to different time zones. Table 9.31 shows its variants.
Table 9.31. AT TIME ZONE Variants
| Expression | Return Type | Description |
|---|---|---|
| timestamp without time zone AT TIME ZONE zone | timestamp with time zone | Treat given time stamp without time zone as located in the specified time zone |
| timestamp with time zone AT TIME ZONE zone | timestamp without time zone | Convert given time stamp with time zone to the new time zone, with no time zone designation |
| time with time zone AT TIME ZONE zone | time with time zone | Convert given time with time zone to the new time zone |
In these expressions, the desired time zone zone can be specified either as a text string (e.g., ‘America/Los_Angeles’ ) or as an interval (e.g., INTERVAL ‘-08:00’ ). In the text case, a time zone name can be specified in any of the ways described in Section 8.5.3.
Examples (assuming the local time zone is America/Los_Angeles ):
The first example adds a time zone to a value that lacks it, and displays the value using the current TimeZone setting. The second example shifts the time stamp with time zone value to the specified time zone, and returns the value without a time zone. This allows storage and display of values different from the current TimeZone setting. The third example converts Tokyo time to Chicago time. Converting time values to other time zones uses the currently active time zone rules since no date is supplied.
The function timezone ( zone , timestamp ) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone .
9.9.4. Current Date/Time
PostgreSQL provides a number of functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction:
CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.
CURRENT_TIME , CURRENT_TIMESTAMP , LOCALTIME , and LOCALTIMESTAMP can optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.
Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “ current ” time, so that multiple modifications within the same transaction bear the same time stamp.
Other database systems might advance these values more frequently.
PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. The complete list of non-SQL-standard time functions is:
transaction_timestamp() is equivalent to CURRENT_TIMESTAMP , but is named to clearly reflect what it returns. statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). statement_timestamp() and transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands. clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command. timeofday() is a historical PostgreSQL function. Like clock_timestamp() , it returns the actual current time, but as a formatted text string rather than a timestamp with time zone value. now() is a traditional PostgreSQL equivalent to transaction_timestamp() .
All the date/time data types also accept the special literal value now to specify the current date and time (again, interpreted as the transaction start time). Thus, the following three all return the same result:
You do not want to use the third form when specifying a DEFAULT clause while creating a table. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used! The first two forms will not be evaluated until the default value is used, because they are function calls. Thus they will give the desired behavior of defaulting to the time of row insertion.
9.9.5. Delaying Execution
The following functions are available to delay execution of the server process:
pg_sleep makes the current session’s process sleep until seconds seconds have elapsed. seconds is a value of type double precision , so fractional-second delays can be specified. pg_sleep_for is a convenience function for larger sleep times specified as an interval . pg_sleep_until is a convenience function for when a specific wake-up time is desired. For example:
The effective resolution of the sleep interval is platform-specific; 0.01 seconds is a common value. The sleep delay will be at least as long as specified. It might be longer depending on factors such as server load. In particular, pg_sleep_until is not guaranteed to wake up exactly at the specified time, but it will not wake up any earlier.
Warning
Make sure that your session does not hold more locks than necessary when calling pg_sleep or its variants. Otherwise other sessions might have to wait for your sleeping process, slowing down the entire system.
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.
Related Links
SQL DATEADD() | DATE_ADD() Syntax
The below syntax is used to perform an arithmatic operations on given a date time value.
For SQL Server
For MS Access
For MySql
SQL DATEADD() | DATE_ADD() Example — Using Expression Or Formula
The following SQL SELECT statement will add some interval time an existing date time value from a given input date value. We use now() function to get the input date value.
For SQL Server
For MS Access
For MySql
The result of above query is:
| Current Date and Time | 10 Seconds Added | -5 Minutes Added | -2 Days Added | 3 Month Added |
|---|---|---|---|---|
| 15-04-2015 14:47:33 | 15-04-2015 14:47:43 | 15-04-2015 14:42:33 | 13-04-2015 14:47:33 | 15-07-2015 14:47:33 |
Sample Database Table — BookOrder
| BookID | BookName | DeliveryDate |
|---|---|---|
| 1 | MySql Concurrency | 26-06-2004 01:01:08 |
| 2 | Oracle PL/sql By Example | 22-09-2001 23:58:06 |
| 3 | Oracle 11g PL/SQL Programming | 20-05-2006 19:28:34 |
| 4 | SQL Programming & Database Design | 24-04-2003 15:41:06 |
| 5 | Pro Oracle Administration | 26-04-2007 18:30:30 |
SQL DATEADD() | DATE_ADD() Example — With Table Column
The following SQL SELECT statement display the column «DeliveryDate», from the «BookOrder» table. The DATEADD() function will add 3 days to «DeliveryDate» and we will stored that value to a new column called «3 Days Added».
For SQL Server
For MS Access
For MySql
The result of above query is:
| DeliveryDate | 3 Days Added |
|---|---|
| 26-06-2004 01:01:08 | 29-06-2004 01:01:08 |
| 22-09-2001 23:58:06 | 25-09-2001 23:58:06 |
| 20-05-2006 19:28:34 | 23-05-2006 19:28:34 |
| 24-04-2003 15:41:06 | 27-04-2003 15:41:06 |
| 26-04-2007 18:30:30 | 29-04-2007 18:30:30 |
Related Links
SQL Dateadd 30 Days
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.
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:
Output:
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:
To understand the above syntax, let us create a table. The query to create a table is as follows:
Step 1: Create Table
Step 2: Insert Data
Insert some records in the table using insert command. The query is as follows:
Step 3: Display records
Display all records from the table using select statement. The query is as follows:
Output:
Step 4: Here is the query to add 30 days value in a table using DATEADD():
Step 5: Now check the table records once again. The query is as follows:
Output:
SQL Dateadd Add Hours
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:
Result :
Example 2: To add hours to an Oracle date you can this simple query:
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)
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:
When we run above query we will get output like as shown below
Output:
SQL Dateadd Add Month
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.
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:
Result:
SQL Dateadd Buisness Days
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:
Swedish bank holidays for december 2013 and january 2014:
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.
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.
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:
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.
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:
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.
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:
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:
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.
Example 3: Let’s LEFT JOIN “holidays” again, to see which days are bank holidays and which ones are not:
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:
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:
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:
Three business days before 2014-01-05:
How many business days from 2013-12-13 to 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.
Result:
| weekdays | |
|---|---|
| 1 | 22 |
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:
Result:
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.»
This query is literally going to generate 100 years’ worth of dates, one per day from 1/1/1950:
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:
3. Exclude the weekends:
Example: Below, I have put the above pieces together:
Result:
| DAYS_INBETWEEN | BUSINESS_DAYS_INBETWEEN |
|---|---|
| 100 | 72 |
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.
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.
Where the variables could be (for example)
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.
SQL Dateadd Interval
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:
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:
Output:
Example 2: Getting a new date of “2020-11-22” after the addition of 3 years to the specified date “2017-11-22”.
Output:
SQL Dateadd Minus 1 Day
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.
Example 2: The following statement returns the current date and time, now minus 1 day :
SQL Dateadd Minus 3 Week
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:
Output:
SQL Dateadd Minus Current Date
Syntax: Here is the syntax to subtract 30 days from current datetime.
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:
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.
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:
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:
Example 4: Add 30 days to a date:
Example 5: Add 3 hours to a date:
Example 6: Subtract 90 minutes from date:
The now() gives the current date time. The method to be used for this is DATE_SUB() from MySQL.
SQL Dateadd Minus Month
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:
Example 1: Below example shows how we can Subtract two months from Current DateTime in Sql Server:
Result:
Example 2: Subtracting 2 months:
Result:
Example 3: Subtracting 1 month:
Result:
SQL Dateadd Minus Year
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:
Result:
SQL Dateadd Negative Number
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:
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:
Notice the use of the negative sign in the number portion.
Result:
Example 3: You can use negative numbers to subtract from the date:
Result:
| Earlier Date |
|---|
| 2010-10-03 00:00:00.000 |
And of course, you can format this using any of the methods previously mentioned.
SQL Dateadd Seconds
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:
Result:
Example 2:
Result:
| DATE_ADD(‘2021-05-01 10:00:00’, INTERVAL 35 SECOND) |
|---|
| 2021-05-01 10:00:35 |
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:
Result:
| DATE_ADD(‘2021-05-01 10:00:00’, INTERVAL 35 SECOND) |
|---|
| 2021-05-01 00:00:35 |
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:
Result:
| DATE_ADD(‘2021-05-01 10:00:00’, INTERVAL 1 MINUTE) |
|---|
| 2021-05-01 10:01:00 |
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:
Result:
SQL Dateadd Subtract
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:
Result:
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.
Result:
Example 3: Our database has a table named Computer with data in the columns Id, Name, and PurchaseDate.
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.
Result:
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’
SQL Dateadd Subtract Quarter
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:
Result:
Example 2: For example, any dates in January, February, or March return the integer 1.
SQL Dateadd Time
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.
Result:
But it is not possible to add it using a time variable:
The error is:
Example 2: Convert time datatype into datetime and add:
Result:
SQL Subtract Date to Today
Use the DATEADD() function if you want to add months or years to the current date.
Syntax:-
- 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.
Assuming today is 2020-09-24, the result is:
| yesterday_date |
|---|
| 2020-09-23 |
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.
You can also calculate a date in the future. For example, to get tomorrow’s date, you add one to current_date:
Example 3: To add or subtract days from a date, you can simply use:
Result:
Example 4: It can be used as:
Output:
SQL Dateadd Date Time
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:
- 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:
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.
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:
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:
Example 5: Let us see how we can combine and date and time in SQL Server.
Method 2: Using DATEADD
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.
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:
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.
Finally, we’ll get a little creative and use a VARCHAR intermediate result to come up with this:
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.
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:
SQL Server Execution Times:
SQL Server Execution Times:
SQL Server Execution Times:
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.
Understanding the DATEADD SQL Function and its use cases
Storing date-time values is very crucial for various business logic and being able to manipulate dates can be very helpful. In this tutorial, we look at how to use the Micrsoft SQL Server DATEADD function to add and subtract from date and time data types.
Solution
Microsoft SQL Server offers multiple database objects to capture date-time data. Today we will talk about the Transact-SQL DATEADD SQL Server function in this tutorial. I will explore an overview and use cases to help you understand the use of this function in a SQL database.
The DATEADD function is used to manipulate SQL date and time values based on some specified parameters. We can add or subtract a numeric value to a specified date-time to get future or past timelines.
The syntax of this function is:
- datepart is a portion of the date to which a specified numeric value will be added
- number is an integer value that will be added to the specified datepart
- date is the value of a specified date to which specified numeric value will be added on specified datepart
The datepart is a part of the date such as the year, month, date, day, hour, minutes, second, etc.
Below is the list of datepart which can be used with the DATEADD function. I have also given its abbreviations which we can use in SQL Server like the abbreviation of Year is yy or yyyy so you can use any of all 3 options Year, yy, or yyyy to manipulate the year datepart. The output returned by all 3 options will be the same. Here is the list.
| Datepart | Abbreviations |
|---|---|
| Year | yy, yyyy |
| Quarter | qq, q |
| Month | mm, m |
| Week | wk,ww |
| Weekday | w, dw |
| Day | dd,d |
| DayOfYear | dy, y |
| Hour | hh |
| Minute | mi, n |
| Second | ss, s |
| Millisecond | ms |
| Microsecond | mcs |
| Nanosecond | ns |
Basic use case of DATEADD Function
I have shown all datepart entities which can be used with the SQL Server DATEADD function. Let’s understand it more using the below example where I have chosen datepart as Year, number as 2, and specified the date as today’s date. I have also given another example where current datetime is calculated using another date-time function GETDATE() as the specified date on which this function will do the calculation to return the result.
The below query will return the date after 2 years from the specified date. It means this function will add 2 years to the year portion of the specified date which is 2021 so the output should return 2023.
As I said above, I have used two approaches to define the current date, one is using the function GETDATE() whereas the second is using a specific date.
The below output has returned 2 years after 5 th Oct 2021 which is 5 th Oct 2023. Both statements returned the same output if we do not consider the time part of the date-time.
As I stated above, we can also use datepart abbreviations in place of datepart names to fulfill our requirements. I have shown this in the below example where I have just changed the abbreviation of Year and used YY and YYYY to return the result.
We can see the result is the same as the above output where we have used datepart name as Year.
Add Date using DATEADD SQL function
This section will explore how to add a numeric value to a datepart of the specified date. I have added a numeric value to multiple dateparts to get future date-time values.
The below query will display the future dates based on specified duration which is the number that we will add to a specified date. I have used MONTH, QUARTER, WEEK, and DAY as datepart, the number 2 as numeric figure or duration which we will add to the respective datepart to get 2nd day, week, quarter or month after the current date which can be returned by either GETDATE() or a specific date.
Below shows the portion of the specified date change which are defined in the DATEADD function. The first column is displaying date after two months since current date, the second column is displaying the second quarter since the current quarter, the third column is displaying the second week after the current week and the fourth column is showing the second day after the current date i.e., 5 th Oct 2021.
The above use case has demonstrated the day, months, and week part, lets do it for the time part of specified date like hours, minutes, and seconds.
I have also used the GETDATE() function to return the current date-time so that you can compare the results of the DATEADD function used in the below query.
The below query will return
- current date-time
- date-time after 2 hours since the current time
- date-time after 2 minutes since the current time
- date-time after 2 seconds since the current time
Let’s analyze the output.
- current date-time returned as 2021-10-05 10:28:01.310
- date-time before 2 hours since current time has returned as 2021-10-05 12:28:01.310
- date-time before 20 minutes since current time has returned as 2021-10-05 10:30:01.310
- date-time before 20 seconds since current time has returned as 2021-10-05 10:28:03.310
You can get similar results for milliseconds, microseconds, and nanoseconds as well by specifying their datepart in the SQL Server DATEADD function.
Subtract Date using DATEADD SQL function
The above section has explained how to add to a date using the DATEADD function whereas this section will show the use cases of subtracting from a date.
We can also get the date-time values from the past by subtracting the numbers to their respective datepart of specified date-time. We need to use a minus (-) with the specified numbers like if you want to return date-time of last year then you can pass -1 to datepart Year.
The below query will return the date-time of the second last month from the current date. The current date will be calculated using another SQL Server function GETDATE(). Similarly, we get the second last quarter, second last week, and second last date from the current date of from a specific specified date. I have used -2 as the number to get the second last datepart values.
Have a look at the output below.
We can use the same logic to subtract the date to get the time part of the specified date-time.
The below query will return
- current date-time
- date-time before 2 hours since current time
- date-time before 20 minutes since current time
- date-time before 20 seconds since current time
Let’s analyze the output.
- current date-time returned as 2021-10-05 10:36:15.960
- date-time before 2 hours since current time has returned as 2021-10-05 08:36:15.960
- date-time before 20 minutes since current time has returned as 2021-10-05 10:16:15.960
- date-time before 20 seconds since current time has returned as 2021-10-05 10:35:55.96960
We can see above results below.
Use DATEADD SQL function with columns
Here, I will show you how to use this function with column values from a table. We will use table OrderDetails and suppose you have a requirement to get the list of products and their sell date, additionally when the company has delivered the product, its return end date, and warranty expiration date.
If the company has a delivery policy for all products within 3 days, warranty expiration is 6 months after the date of purchase, and return end date is within 3 weeks from its purchase.
We can use the below query to get this information. I have used the DATEADD function with DAY, MONTH, and WEEK datepart and the date column of table OrderDetails which stores the product sell date.
We can see the details about each product and its purchase date in column Date. You can also see the additional columns that we added DeliveryDate, WarrantyEndDate, , and ReturnEndDate. You can compare the dates of each column to validate the output.
Next Steps
- I have explored the DATEADD SQL function in this article along with use cases. I have also explained how to use this function with the values specified in a column. You can go ahead and practice to return results for other dateparts.
- Check out these related date function tutorials:
- SQL DATE Functions
- SQL Server Date and Time Functions with Examples
- SQL Server DATEADD Function
- Add and Subtract Dates using DATEADD in SQL Server
- DATEDIFF SQL Server Function
- SQL Server DATEDIFF Function
- SQL Server DateDiff Example
- Date Format in SQL Server
- Date and Time Conversions Using SQL Server
- SQL Server DATENAME Function
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.SQL Server DATEADD Function
Summary: in this tutorial, you will learn how to use the SQL Server DATEADD() function to add a specified value to a specified date part of a date.
SQL Server DATEADD() function overview
The DATEADD() function adds a number to a specified date part of an input date and returns the modified value.
The following shows the syntax of the DATEADD() function:
The DATEADD() function accepts three arguments:
- date_part is the part of date to which the DATEADD() function will add the value . (See the valid date parts in the table below)
- value is an integer number to be added to the date_part of the input_date . If the value evaluates to a decimal or float, the function DATEADD() will truncate the decimal fraction part. It will not round the number in this case.
- input_date is a literal date value or an expression which can resolve to a value of type DATE , DATETIME , DATETIMEOFFSET , DATETIME2 , SMALLATETIME , or TIME
The following table lists the valid values of date_part :
date_part abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww hour hh minute mi, n second ss, s millisecond ms microsecond mcs nanosecond ns The function DATEADD() function returns a new date value after adding the value to the date_part .
SQL Server DATEADD() function examples
Let’s take some examples of using the DATEADD() function.
Add 1 second to 2018-12-31 23:59:59
Here is the output
Adding 1 day to 2018-12-31 00:00:00:
This example uses the DATEADD() function to calculate the estimated shipped date based on the ordered date:
The following picture shows the partial output:
Handling month examples
If you add a number of months to a date and the day of the date result does not exist, the DATEADD() function will return the last day of the return month.
See the following example:
In this example, the month of the return date is September. However, day 31 does not exist in September, therefore, the DATEADD() function returns the last day of September (30) as the day for the result date:
Notice that the following query returns the same result:
In this tutorial, you have learned how to use the SQL Server DATEADD() function to add a specified value to a date part of a date.