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


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


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

ankur morabia said...
This comment has been removed by the author.
ankur morabia 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;