Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > How to extract day/month/year from a DateTime column – TSQL

How to extract day/month/year from a DateTime column – TSQL


You can do this using two different ways. First is to us DAY(), MONTH() an YEAR() TSQL functions. These functions return an integer representing a day/month or year respectively.

These can be used as:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      DAY  ( GETDATE() ) AS 'Day',

            MONTH( GETDATE() ) AS 'Month',

            YEAR ( GETDATE() ) AS 'Year'

Result Set:

Day         Month       Year

———– ———– ———–

19          5           2011

 

(1 row(s) affected)

 

Another way is to use DATEPART() TSQL function. The DATEPART() function can also extract week, hour, minute, second in addition to day, month and year. For a full list of parts that can be extracted using DATEPART() refer BOL.

We can use DATEPART() to extract parts as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      DATEPART(DAY,   GETDATE()) AS 'Day',

            DATEPART(MONTH, GETDATE()) AS 'Month',

            DATEPART(YEAR,  GETDATE()) AS 'Year',

            DATEPART(HOUR,   GETDATE()) AS 'Hour',

            DATEPART(MINUTE, GETDATE()) AS 'Minute',

            DATEPART(SECOND, GETDATE()) AS 'Second'

Result Set:

Day         Month       Year        Hour        Minute      Second

———– ———– ———– ———– ———– ———–

19          5           2011        21          6           5

 

(1 row(s) affected)

Personally I prefer to use DAY(), MONTH() an YEAR() functions as it only need one argument and easier to type… However, when we need to extract hour, minute and so on we need to use DATEPART() function.

 

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.