Sunday, May 30, 2021

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


Saturday, May 29, 2021

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