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