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)

Read more: SqlServerPedia

Posted via email from Jasper-net