March 22, 2008

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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

6 Responses to "How to extract Year, Month, Day, Hour, Minute and Seconds from a DateTime"
  1. ImnVerted said...
    April 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.

  2. Suprotim Agarwal said...
    April 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.

  3. Anonymous said...
    August 9, 2012 at 8:14 AM

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

  4. dani said...
    January 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!!

  5. ankur morabia said...
    August 22, 2013 at 9:53 PM
    This comment has been removed by the author.
  6. ankur morabia said...
    August 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;

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions