Saturday, 18 February 2017

How to get Date and Time parts using SQL Server DateName and DatePart Functions

SQL Server DateName and DatePart Functions to get Date and Time Parts from DateTime Values
Use Tamatam
Declare @StDate as DATETIME = '08/20/2015 15:25:45' --'MM/DD/YYYY HH:MM:SS'
Declare @ToDate as DATETIME --'MM/DD/YYYY HH:MM:SS AM/PM'
SET @ToDate = GETDATE()
-------------------------------------------------------------------------------------------------
SELECT DateName(DAY,@StDate) AS Day_No_in_Month
SELECT DatePart(DAY,@StDate) AS Day_No_in_Month
SELECT DatePart(D,@StDate) AS Day_No_in_Month
SELECT DatePart(DD,@StDate) AS Day_No_in_Month

--Output Result as '20' ; The Day number in Month of Date
-------------------------------------------------------------------------------------------------

SELECT DatePart(DAYOFYEAR,@StDate) AS Days_No_in_Year
SELECT DateName(Y,@StDate) AS Days_No_in_Year
SELECT DatePart(Y,@StDate) AS Days_No_in_Year

--Output Result as '232' ; The Day Number in Year of Date
-------------------------------------------------------------------------------------------------

SELECT DateName(WEEK,@StDate) AS WK_No_in_Year
SELECT DatePart(WEEK,@StDate) AS WK_No_in_Year
SELECT DatePart(WK,@StDate) AS WK_No_in_Year

--Output Result as '34' ; The Week Number in Year of Date
-------------------------------------------------------------------------------------------------

SELECT DatePart(WEEKDAY,@StDate) AS WK_No_in_Week
SELECT DatePart(W,@StDate) AS WK_No_in_Week

--Output Result as '5' ; The Weekday Number in Week of Date
-------------------------------------------------------------------------------------------------

SELECT DateName(W,@StDate) AS WK_Day_Name
SELECT DateName(DW,@StDate) AS WK_Day_Name
SELECT Left(DateName(W,@StDate),3) AS WK_Day_Name

--Output Result as 'Thursday' ; The Weekday Name in Week of Date
-------------------------------------------------------------------------------------------------

SELECT DatePart(MONTH,@StDate) AS Month_Number
SELECT DatePart(MM,@StDate) AS Month_Number
SELECT DatePart(M,@StDate) AS Month_Number

--Output Result as '8' The Month Number of Date
-------------------------------------------------------------------------------------------------

SELECT DateName(MONTH,@StDate) AS Month_Name
SELECT DateName(MM,@StDate) AS Month_Name
SELECT DateName(M,@StDate) AS Month_Name
--Output Result as 'August' The Month Name of Date
-------------------------------------------------------------------------------------------------

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 DateName(YY,@StDate) AS Year_Nummber
SELECT DatePart(YY,@StDate) AS Year_Nummber
--Output Result as '2015' The Year Number of Date

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

SELECT DATEPART(HOUR,@StDate) AS Hours_from_DateTime
--Output Result as '15' ; 'HH'

SELECT DATEPART(MINUTE,@StDate) AS Minutes_from_DateTime
--Output Result as '25' ; 'MM'

SELECT DATEPART(SECOND,@StDate) AS Seconds_from_DateTime
--Output Result as '45' ; 'SS'
GO
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------

Happy Learning...
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts