SQL Server DATEADD() Function

The DATEADD() function adds a time/date interval to a date and then returns the date.

Syntax:

DATEADD (datepart , number , datetime_expr)

To add a time measure, the number must be positive. To subtract a time measure, the number must be negative

datepart abbreviations can be used below.

• year, yyyy, yy = Year
• quarter, qq, q = Quarter
• month, mm, m = month
• dayofyear, dy, y = Day of the year
• day, dd, d = Day
• week, ww, wk = Week
• weekday, dw, w = Weekday
• hour, hh = hour
• minute, mi, n = Minute
• second, ss, s = Second
• millisecond, ms = Millisecond

NOTE: DATEADD also accepts abbreviations in the datepart parameter. Use of these abbreviations is generally discouraged as they can be confusing (m vs mi, ww vs w, etc.).

DECLARE @Date DATETIME2 = GETDATE();
SELECT @Date; --2021-05-30 17:01:30.2333333
SELECT DATEADD(YEAR, 1, @Date) --2022-05-30 17:01:30.2333333
SELECT DATEADD(QUARTER, 1, @Date) --2021-08-30 17:01:30.2333333
SELECT DATEADD(WEEK, 1, @Date) --2021-06-06 17:01:30.2333333
SELECT DATEADD(DAY, 1, @Date) --2021-05-31 17:01:30.2333333
SELECT DATEADD(HOUR, 1, @Date) --2021-05-30 18:01:30.2333333
SELECT DATEADD(MINUTE, 1, @Date) --2021-05-30 17:02:30.2333333
SELECT DATEADD(SECOND, 1, @Date) --2021-05-30 17:01:31.2333333
SELECT DATEADD(MILLISECOND, 1, @Date)--2021-05-30 17:01:30.2343333

Date and Time formatting using Format() function in SQL Server

Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.

Now Let's see the below example. I have demonstrated all the output for the given argument.

Example:

DECLARE @Date DATETIME = Getdate()

SELECT FORMAT(@Date, N'dddd, MMMM dd, yyyy hh:mm:ss tt')

Output:

Sunday, May 30, 2021 01:28:44 PM

Argument	Output
yyyy	2021
yy	21
MMMM	May
MM	05
M	5
dddd	Sunday
ddd	Sun
dd	30
d	30
HH	13
H	13
hh	01
h	1
mm	28
m	28
ss	44
s	44
tt	PM
t	P
fff	597
ff	59
f	5

You can also supply a single argument to the FORMAT() function to generate a pre-formatted output.

DECLARE @Date DATETIME = GETDATE()

SELECT FORMAT(@Date, N'U')

Argument	Output
D	Sunday, May 30, 2021
d	5/30/2021
F	Sunday, May 30, 2021 1:44:12 PM
f	Sunday, May 30, 2021 1:44 PM
G	5/30/2021 1:44:12 PM
g	5/30/2021 1:44 PM
M	May 30
O	2021-05-30T13:44:12.4870000
R	Sun, 30 May 2021 13:44:12 GMT
s	2021-05-30T13:44:12
T	1:44:12 PM
t	1:44 PM
U	Sunday, May 30, 2021 8:14:12 AM
u	2021-05-30 13:44:12Z
Y	May 2021

Note: The above list is using the en-US culture. A different culture can be specified for the FORMAT() via the third parameter.

DECLARE @Date DATETIME = '2016-09-05 00:01:02.333'

SELECT FORMAT(@Date, N'U', 'zh-cn')

Output:

2021年5月30日 10:10:53

Date and Time formatting using CONVERT() Function in SQL Server

You can use the CONVERT function to cast a datetime datatype to a formatted string.

You can also use some built-in codes to convert into a specific format. Here are the options built into SQL Server.

SQL Code:

SELECT GETDATE() AS [Result] --2021-05-29 18:50:18.737

DECLARE @convert_code INT = 100 

SELECT CONVERT(VARCHAR(30), GETDATE(), @convert_code) AS [Result]

Output:

@convert_code	Result
100	May 29 2021  6:50PM
101	05/29/2021
102	2021.05.29
103	29/05/2021
104	29.05.2021
105	29-05-2021
106	29 May 2021
107	May 29, 2021
108	18:50:18
109	May 29 2021  6:50:18:737PM
110	05-29-2021
111	2021/05/29
112	20210529
113	29 May 2021 18:50:18:737
114	18:50:18:737
120	2021-05-29 18:50:18
121	2021-05-29 18:50:18.737
126	2021-05-29T18:50:18.737
127	2021-05-29T18:50:18.737
130	18 ???? 1442  6:50:18:737PM
131	18/10/1442  6:50:18:737PM

Display column names separated with comma

We can achieve this by STUFF Function. I have used it as stored procedure.

See Detail about STUFF Function: STUFF Function with example

SQL Code:

CREATE PROCEDURE TableColumns (@Table VARCHAR(100))
AS
SELECT ColumnNames =
STUFF( (SELECT ', ' + c.name
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID( @Table)
FOR XML PATH('')),
1, 1, '')
GO

Execute and run the above store procedure.

exec TableColumns 'Employee_Details'

Output:

ColumnNames

 EmployeeId, Name, SSN, DepartmentCode, IntraDepartmentCode

SQL Server STUFF() Function with example

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position

STUFF() Function can be used in various ways. Let's see the below examples.

Basic Character Replacement with STUFF()

The STUFF() function inserts a string into another string by first deleting a specified number of characters. The following example, deletes "Svr" and replaces it with "Server". This happens by specifying the start_position and length of the replacement.

SELECT STUFF('SQL Svr Documentation', 5, 3, 'Server')

Output:

SQL Server Documentation

Basic Example of STUFF() function

STUFF(Original_Expression, Start, Length, Replacement_expression)

STUFF() function inserts Replacement_expression, at the start position specified, along with removing the characters specified using Length parameter.

Use the below table as example:

Table Name : ProductReview

ProductReviewID	ReviewerName	EmailAddress
1	John Smith	john@fourthcoffee.com
2	David	david@graphicdesigninstitute.com
3	Jill	jill@margiestravel.com
4	Laura Norman	laura@treyresearch.net

SQL Code:

Select ProductReviewID, ReviewerName, STUFF(EmailAddress, 2, 2, '*****') as StuffedEmail From ProductReview

Output:

ProductReviewID	ReviewerName	StuffedEmail
1	John Smith	j*****n@fourthcoffee.com
2	David	d*****id@graphicdesigninstitute.com
3	Jill	j*****l@margiestravel.com
4	Laura Norman	l*****ra@treyresearch.net

Using FOR XML to Concatenate Values from Multiple Rows

Below is the example, used above mentioned table. Reviewer name is separated by ;

SQL Code:

SELECT
STUFF( (SELECT ';' + ReviewerName
FROM ProductReview
where (ReviewerName is not null and ReviewerName <> '')
ORDER BY ReviewerName ASC
FOR XML PATH('')),
1, 1, '')

Output:

David;Jill;John Smith;Laura Norman