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.


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

6 comments:

imnverted said...

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.

Suprotim Agarwal said...

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.

Anonymous said...

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

dani said...

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!!

Unknown said...
This comment has been removed by the author.
Unknown said...

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;