How to get Day/Week/Month/Year/DayOfYear/WeekDay/Hour/Min/Sec .etc from DateTime

It’s always been tricky for T-SQL Developer to Datepart the Date. With DATENAME Function it has become easy and manageable.

DATENAME can be used for below Datepart

YEAR

QUARTER

MONTH

DAYOFYEAR

DAY

WEEK

WEEKDAY

HOUR

MINUTE

SECOND

MILLISECOND

MICROSECOND

NANOSECOND

TZOFFSET

 

SELECT

convert(datetime,convert(varchar(10),GETDATE(),101)) Date_As,

Day(GETDATE())Day_As,MONTH(GETDATE()) Month_As,YEAR(GETDATE())Year_As,

DATENAME(DW,GETDATE()) DayName_As,

DATENAME(MONTH,GETDATE())MonthName_As,DATENAME(QUARTER,GETDATE()) QuarterNo_As


 

 

Examples

 

 

 

2 thoughts on “How to get Day/Week/Month/Year/DayOfYear/WeekDay/Hour/Min/Sec .etc from DateTime

  1. I have a projects on Data warehouse(as its DWH so the datetime will be historical as well) so on ETL step I want to get datetime from any table attribute and then I want to split datetime into year, month, day, hr, sec, etc can u tell me how and where to put this code for etl
    I am using SQL Server Business Intelligence development studio so ur guidance will be very hekp helpful for me

    1. How you r pulling the data from the database, To get to this u can put this in the T-SQL statment while pulling the data

      SELECT convert(datetime,convert(varchar(10),GETDATE(),101)) Date_As,Day(GETDATE())Day_As,MONTH(GETDATE()) Month_As,YEAR(GETDATE())Year_As,
      DATENAME(DW,GETDATE()) DayName_As,DATENAME(MONTH,GETDATE())MonthName_As,DATENAME(QUARTER,GETDATE()) QuarterNo_As FROM “TABLENAME”

      Hope it aswers ur question

Leave a Reply

Your email address will not be published. Required fields are marked *