SQL Date functions and Formats

SQL Functions: 

In this article, we will explore various SQL functions and Convert Date formats to use in writing SQL queries.

SYSDATETIME() - to get current system date

GETUTCDATE() - to get current UTC date

GETDATE() - to get current system date

DATEDIFF() - Diff between two dates

DATEPART() - to get some part of date for example - if you want to retrieve year part (2020) from date 12/09/2020

SQL Convert Date Formats:

We can use the SQL CONVERT() function in SQL Server to format DateTime in various formats.

Syntax for the SQL CONVERT() function is as follows.

SELECT CONVERT (data_type(length)),Date, DateFormatCode)

Data_Type: We need to define data type along with length. In the date function, we use Varchar(length) data types

Date: We need to specify the date that we want to convert

DateFormatCode: We need to specify DateFormatCode to convert a date in an appropriate form. We will explore more on this in the upcoming section

Employee Detail table:

Select * from EmployeeDetail 


Exercise Queries:

--1. Show "JoiningDate" in "dd mmm yyyy" format, ex- "15 Mar 2020"
SELECT CONVERT(VARCHAR(20),JoiningDate,106) AS ResultDateFormat FROM EmployeeDetail

--2. Show "JoiningDate" in "yyyy/mm/dd" format, ex- "2013/02/21" 
SELECT CONVERT(VARCHAR(20),JoiningDate,111) AS ResultDateFormat FROM EmployeeDetail

--3. Show only time part of the "JoiningDate". ex- hh:mm: ss
SELECT CONVERT(VARCHAR(20),JoiningDate,108) AS ResultDateFormat FROM EmployeeDetail

--4. Get only Year part of "JoiningDate".
SELECT DATEPART(YEAR,JoiningDate) AS ResultDateFormat FROM EmployeeDetail

--5. Get only Month part of "JoiningDate".
SELECT DATEPART(MM,JoiningDate) AS ResultDateFormat FROM EmployeeDetail

--6. Get only Day part of "JoiningDate".
SELECT DATEPART(DD,JoiningDate) AS ResultDateFormat FROM EmployeeDetail

--7. Get system date.
SELECT SYSDATETIME() 
SELECT GETDATE()

--8. Get UTC date. 
SELECT GETUTCDATE()

/*--9. Get the first name, current date, joiningdate and diff between 
current date and joining date in months. */

SELECT FirstName,GETDATE() AS [Current Date], JoiningDate
,DATEDIFF(MM,JoiningDate,GETDATE()) AS TotalMonths FROM EmployeeDetail

/*--10.Get the first name, current date, joiningdate and diff between 
current date and joining date in days.*/

SELECT FirstName,GETDATE() AS [Current Date], JoiningDate
,DATEDIFF(DD,JoiningDate,GETDATE()) AS TotalDays FROM EmployeeDetail

--11. Get all employee details from EmployeeDetail table whose joining year is 2008.
SELECT * FROM EmployeeDetail WHERE DATEPART(YYYY,JoiningDate)='2008'

--12. Get all employee details from EmployeeDetail table whose joining month is Jan(1).
 SELECT * FROM EmployeeDetail WHERE DATEPART(MM,JoiningDate)='1'

/*--13.Get all employee details from EmployeeDetail table whose joining date between 
"2008-01-01" and "2010-12-01".*/
 
 SELECT * FROM EmployeeDetail WHERE JoiningDate BETWEEN '2008-01-01'
 AND '2010-12-01' 

Please refer below YouTube video for more detail explanation:


How to find 2nd Lowest Salary | Find Nth Lowest Salary | 4 ways to get it

 EmployeeDetail table:

Four ways to get Nth lowest salary in EmployeeDetail table:

Select * from EmployeeDetail order by Salary 
---------------------1st Method----------------------------
--Using sub-query (With > Operator):
--2nd Highest Salary
Select min(Salary) As Salary from EmployeeDetail where 
Salary > (Select min(Salary) from EmployeeDetail)

--3rd Highest Salary
Select min(Salary) As Salary from EmployeeDetail where 
Salary > (Select min(Salary) from EmployeeDetail Where
Salary > (Select min(Salary) from EmployeeDetail))

---------------------2nd Method----------------------------
--Using sub-query (With Not In Operator)
Select min(Salary) As Salary from EmployeeDetail where 
Salary NOT IN (Select min(Salary) from EmployeeDetail)

---------------------3rd Method----------------------------
--Using correlated sub-query
Select distinct Salary from EmployeeDetail A
Where 2=(Select count(distinct salary) from EmployeeDetail B 
where A.Salary >= b.Salary)

---------------------4th Method-----------------------------
--Using TOP Clause keyword 
Select top 1 Salary From
(Select distinct top 2 Salary from 
EmployeeDetail Order by Salary)
As Temp Order by Salary DESC

/*NOTE: First three method won't consider NULL however 4th method 
will consider NULL values*/

Please find the below Youtube video:

SQL HAVING Clause

What is having Clause?

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

A HAVING clause in SQL specifies that an SQL SELECT statement must only return rows where aggregate values meet the specified conditions. After the aggregating operation, HAVING is applied, filtering out the rows that don't match the specified conditions.

Why we cannot use where with aggregate function?

We cannot use the WHERE clause with aggregate functions because it works for filtering individual rows. In contrast, HAVING can works with aggregate functions because it is used to filter groups.

Basic Syntax:

SELECT column_names

FROM tableName

WHERE condition

GROUP BY column_names

HAVING condition

ORDER BY column_names

EmployeeDetail Table:

ProjectDetail Table:

Below Sample Queries using Having Clause:

/*Write Down the query to fetch the number of employees in each department.
Only include department with more than 2 employees */

SELECT Department, count(EmpID) As NoOfEmploees 
FROM EmployeeDetail
GROUP BY Department
HAVING count(EmpID)>2
ORDER BY count(EmpID) DESC

--Write Down the query to fetch Project name assign more than one employee

SELECT ProjectName, count(*) As NoOfEmployees 
FROM ProjectDetail
GROUP BY ProjectName
HAVING count(*)>1

--Write Down the query to fetch Employee name(FirstName) assign more than one Project.

SELECT ed.FirstName,Count(ProjectName) NoOfProjects FROM EmployeeDetail ed
INNER JOIN ProjectDetail pd
ON ed.EmpId=pd.EmpId
GROUP BY FirstName
HAVING count(*)>1

/*Write Down the query to fetch if the Employees 'Frank' or 'Tom' have assigned with 
more than 2 Projects*/

SELECT ed.FirstName,Count(ProjectName) NoOfProjects FROM EmployeeDetail ed
INNER JOIN ProjectDetail pd
ON ed.EmpId=pd.EmpId
WHERE ed.FirstName='Frank' OR ed.FirstName='Tom'
GROUP BY FirstName
HAVING count(*)>1

Please refer below YouTube video on HAVING Clause: