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

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

 

 

 

Categories: Uncategorized
  1. Tahir
    August 20, 2011 at 10:00 am

    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

    • msufian
      August 23, 2011 at 6:19 pm

      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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: