Saturday, February 18, 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_DB GO
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
-------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog