Friday, May 22, 2015

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 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. 

1 comment:

Popular Posts