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: