Archive

Posts Tagged ‘year’

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

19.05.2011 No comments

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