SQL - How to convert DateTime into different Date Time Formats in SQL

In the daily SQL activities, we need to get the different types of the date formats to meet the business expectations. To avoid any failure, it's advisable to store your data in Varchar (24) format. This is because we can convert them in date format easily. 

Based on my experience, there are some common date formats to short out the different types of date format which are given below:

DECLARE @DT DATETIME
SELECT @DT=GETDATE()
SELECT @DT AS [DATE TIME],
CONVERT(VARCHAR,@DT,112) AS [YYYYMMDD],
CONVERT(VARCHAR,@DT,101) AS [MM/DD/YYYY],
CONVERT(VARCHAR,@DT,102) AS [YYYY.MM.DD],
CONVERT(VARCHAR,@DT,103) AS [DD/MM/YYYY],
CONVERT(VARCHAR,@DT,104) AS [DD.MM.YYYY],
CONVERT(VARCHAR,@DT,105) AS [DD-MM-YYYY],
CONVERT(VARCHAR,@DT,106) AS [DD MMM YYYY],
CONVERT(VARCHAR,@DT,107) AS [MMM DD, YYYY],
CONVERT(VARCHAR,@DT,108) AS [HH:MM:SS],
CONVERT(VARCHAR,@DT,112)+''+REPLACE(CONVERT(VARCHAR(8),@DT,114),':','') AS [YYYYMMDDHHMMSS]

Result outputs -

DATE TIME	YYYYMMDD	MM/DD/YYYY	YYYY.MM.DD	DD/MM/YYYY	DD.MM.YYYY
05/22/2015 15:43:22	20150522	05/22/2015	2015.05.22	22/05/2015	22.05.2015

DD-MM-YYYY	DD MMM YYYY	MMM DD, YYYY	HH:MM:SS	YYYYMMDDHHmmss
22-05-2015	22 May 2015	22 May, 2015	15:43:22	20150522154322

SQL – EOMONTH()

SQL Server 2012 has introduced an EOMONTH function to get the last month of the any date which was very tricky before that.

Today, we are going to use this function to get the last date of the current month, previous month, next month and the same for the next year as well as for previous year. This function is used to save your work and improve the performance of your SQL script.