Create function to calculate age on specific date

 This function will take 2 datetime parameters, the DOB, and a date to check the age at.

CREATE FUNCTION [dbo].[Calc_Age]
(
@DOB datetime , @calcDate datetime
)
RETURNS int
AS
BEGIN
declare @age int
IF (@calcDate < @DOB )
RETURN -1
-- If a DOB is supplied after the comparison date, then return -1
SELECT @age = YEAR(@calcDate) - YEAR(@DOB) +
CASE WHEN DATEADD(year,YEAR(@calcDate) - YEAR(@DOB)
,@DOB) > @calcDate THEN -1 ELSE 0 END
RETURN @age
END

Check the age:

SELECT dbo.Calc_Age('2010-04-13',Getdate())

Output

11

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

SQL Server Select into statement with example

The SELECT INTO statement creates a new table and inserts rows from the query into it.

If you want to copy the partial data from the source table, you use the WHERE clause to specify which rows to copy. Similarly, you can specify which columns from the the source table to copy to the destination table by specifying them in the select list.

Note that SELECT INTO statement does not copy constraints such as primary key and indexes from the source table to the destination table.

Syntax:

SELECT Column1, Column2, Column3
INTO MyNewTable
FROM MySourceTable;

Let's see with below examples:

• Create a backup copy of Customers:

SELECT * INTO Customers2017
FROM Customers;

• Copy records from one database to another.

SELECT * INTO Rohit.dbo.PurchaseOrderDetail  
FROM AdventureWorks2017.purchasing.PurchaseOrderDetail

• Copy only a few columns into a new table:

SELECT CustomerName, ContactName INTO Customers2017
FROM Customers;

• Copies only the US customers into a new table:

SELECT * INTO CustomersUS
FROM Customers
WHERE Country = 'US';

SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data.

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

Create Employee and Manager Hierarchy using Common Table Expression

We will see how to get the Employee-Manager hierarchy from the same table.

Let's setup the table first.

CREATE TABLE dbo.Employee
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
ManagerID INT NULL
)

insert values into the table

INSERT INTO Employee VALUES (101, 'Ken', 'Sánchez', NULL)
INSERT INTO Employee VALUES (102, 'Keith', 'Hall', 101)
INSERT INTO Employee VALUES (103, 'Fred', 'Bloggs', 101)
INSERT INTO Employee VALUES (104, 'Joseph', 'Walker', 102)
INSERT INTO Employee VALUES (105, 'Zydr', 'Klyb', 101)
INSERT INTO Employee VALUES (106, 'Sam', 'Jackson', 105)
INSERT INTO Employee VALUES (107, 'Peter', 'Miller', 103)
INSERT INTO Employee VALUES (108, 'Chloe', 'Samuels', 105)
INSERT INTO Employee VALUES (109, 'George', 'Weasley', 105)
INSERT INTO Employee VALUES (110, 'Michael', 'Kensington', 106)

See the values below.

EmployeeID	FirstName	LastName	ManagerID
101	Ken	Sánchez	NULL
102	Keith	Hall	101
103	Fred	Bloggs	101
104	Joseph	Walker	102
105	Zydr	Klyb	101
106	Sam	Jackson	105
107	Peter	Miller	103
108	Chloe	Samuels	105
109	George	Weasley	105
110	Michael	Kensington	106

Below SQL Code to demonstrate the Employee and Manager relationship.

;WITH cteReports (EmpID, FirstName, LastName, SupervisorID, EmpLevel) AS
(
	SELECT EmployeeID, FirstName, LastName, ManagerID, 1
	FROM Employee
	WHERE ManagerID IS NULL
	UNION ALL
	SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1
	FROM Employee AS e
	INNER JOIN cteReports AS r ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employee WHERE EmployeeID = cteReports.SupervisorID)
AS ManagerName
FROM cteReports
ORDER BY EmpLevel, SupervisorID

Output:

FullName	EmpLevel	ManagerName
Ken Sánchez	1	NULL
Keith Hall	2	Ken Sánchez
Fred Bloggs	2	Ken Sánchez
Zydr Klyb	2	Ken Sánchez
Joseph Walker	3	Keith Hall
Peter Miller	3	Fred Bloggs
Sam Jackson	3	Zydr Klyb
Chloe Samuels	3	Zydr Klyb
George Weasley	3	Zydr Klyb
Michael Kensington	4	Sam Jackson