How to extract Year, Month, Day, Hour, Minute and Seconds from a DateTime

I recently saw a post in the forums where a user wanted to extract the Year, Month, Day, Hour, Minute and Seconds from a DateTime field. Let us see how easily we can do it using the DATEPART() function. The DATEPART function accepts two parameters :

DATEPART ( datepart , date ) where
datepart - specifies the part of the date to return. For eg: year, month and so on
date - is the datetime or smalldatetime value

QUERY

SELECT
DATEPART(year, GETDATE()) as 'Year',
DATEPART(month,GETDATE()) as 'Month',
DATEPART(day,GETDATE()) as 'Day',
DATEPART(week,GETDATE()) as 'Week',
DATEPART(hour,GETDATE()) as 'Hour',
DATEPART(minute,GETDATE()) as 'Minute',
DATEPART(second,GETDATE()) as 'Seconds',
DATEPART(millisecond,GETDATE()) as 'MilliSeconds'

Note: When using a smalldatetime, only information up to the 'minute' gets displayed. Seconds and milliseconds are always 0.
at

6 comments:

  1. imnvertedApril 29, 2008 at 2:38 PM

    I cannot tell you how relieved I am to FINALLY find this section of code. And thank you for showing HOW it should be used. Im new to creating SQL queries and only finding sections of code with no understanding how they are to be used is enough to make me crazy.

    ReplyDelete
  2. Suprotim AgarwalApril 29, 2008 at 8:33 PM

    You are most welcome imnverted!

    There are plenty of other such tips tricks which you can view through the Categories on the left hand side.

    ReplyDelete
  3. AnonymousAugust 9, 2012 at 8:14 AM

    Thanks. This saved me A LOT of (Years, Months, Days, Hours, Minutes)!!!

    ReplyDelete
  4. daniJanuary 1, 2013 at 10:51 AM

    Hello, is it possible to ask you something about this old post?
    The SQL table I'm using it has DATE data type instead of DATETIME, so when trying to get the Month, I get this error:

    Arithmetic overflow error converting expression to data type datetime.

    I'm lost. Here is my Query:

    SELECT month(INVDATE) as myMonth FROM [dbo].[OEINVH]

    Could you give me a hand please?
    Thank you!!

    ReplyDelete
  5. UnknownAugust 22, 2013 at 9:53 PM

    This comment has been removed by the author.

    ReplyDelete
  6. UnknownAugust 22, 2013 at 9:56 PM

    First store date value in table.It can be done using sysdate();
    Then go following query

    select extract(year from datecoloumn) from table;
    select extract(month from datecoloumn) from table;
    select extract(date from datecoloumn) from table;

    ReplyDelete