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_db
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'

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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