Saturday, 18 February 2017

How to Format the Date Time values with CONVERT and FORMAT Functions in SQL Server

SQL Server CONVERT and FORMAT Functions to Format the Date Time Values 
USE TAMATAM
GO
Declare @StDate as DATETIME = '08/20/2015 15:25:45' --'MM/DD/YYYY HH:MM:SS'
'Please note that below Conversions will convert your dates from Date format to Varchar format, which may impact your date calculations.

SELECT CONVERT(VARCHAR(20),@StDate,100) AS Date_Format_Default 
--Output Result in Default Format as 'Aug 20 2015 3:25PM'

SELECT CONVERT(VARCHAR(20),@StDate,101) AS Date_Format_USA 
--Output Result in USA Format as '08/20/2015' --'MM/DD/YYYY'

SELECT CONVERT(VARCHAR(20),@StDate,1) AS Date_Format1_USA
--Output Result in Default Format as '08/20/15'  --'MM/DD/YY'

SELECT CONVERT(VARCHAR(20),@StDate,10) AS Date_Format2_USA
--Output Result in USA Format as '08-20-15' --'MM-DD-YY'

SELECT CONVERT(VARCHAR(20),@StDate,110) AS Date_Format3_USA
--Output Result in USA Format as '08-20-2015' --'MM-DD-YYYY'

SELECT CONVERT(VARCHAR(20),@StDate,2) AS Date_Format1_ANSI
--Output Result in ANSI Format as '15.08.20' --'YY.MM.DD'

SELECT CONVERT(VARCHAR(20),@StDate,102) AS Date_Format_ANSI
--Output Result in ANSI Format as '2015.08.20' --'YYYY.MM.DD'

SELECT CONVERT(VARCHAR(20),@StDate,103) AS Date_Format_UK_INDIA
--Output Result in UK_INDIA Format as '20/08/2015' --'DD/MM/YYYY'

SELECT CONVERT(VARCHAR(20),@StDate,3) AS Date_Format1_UK_INDIA
--Output Result in UK_INDIA Format as '20/08/2015' --'DD/MM/YY'

GO
------------------------------------------------------------------------------------------------------------------------
Declare @StDate as DATETIME = '08/20/2015 15:25:45' --'MM/DD/YYYY HH:MM:SS'

SELECT DAY(@StDate) AS Day_in_Date
--Output Result as '20' ; 'DD'

SELECT MONTH(@StDate) AS Month_in_Date
--Output Result as '8' ; 'M'

SELECT DateName(Q,@StDate) AS Quarter_Nummber
SELECT DatePart(Q,@StDate) AS Quarter_Nummber
SELECT DateName(QQ,@StDate) AS Quarter_Nummber
SELECT DatePart(QQ,@StDate) AS Quarter_Nummber
--Output Result as '3' The Quarter Number of Date

SELECT YEAR(@StDate) AS Year_in_Date
--Output Result as '2015' ; 'YYYY'

SELECT CONVERT(DATE,@StDate) AS Date_from_DateTime_ANSI_Format
--Output Result as '2015-08-20' ; 'YYYY-MM-DD'

SELECT FORMAT(CONVERT(DATE,@StDate),'dd/MM/yyyy') AS Date_from_DateTime_Custom_Format

SELECT FORMAT(@StDate,'dd/MM/yyyy') AS Date_from_DateTime_Custom_Format
--Output Result as '20/08/2015' ; 'DD/MM/YYYY'

SELECT CONVERT(VARCHAR(10),@StDate,108) AS Time_from_DateTime
--Output Result as '15:25:45' ; 'HH:MM:SS'

SELECT FORMAT(@StDate,'hh:mm tt') AS Time_AmPm_Format
--Output Result as '03:25 PM' ; 'HH:MM AM/PM'

SELECT FORMAT(@StDate,'dd/MM/yyyy hh:mm tt') AS DateTime_AmPm_Format
--Output Result as '20/08/2015 03:25 PM' ; 'DD/MM/YYYY HH:MM:SS AM/PM'


------------------------------------------------------------------------------------------------------------------------

Happy Learning...
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts