How get date and time parts in SQL Server 2012?

For get date and time parts in SQL Server 2012 are functions. Step by step I describe several of them.

The first function is DAY(date) that returns an integer representing day of the month of the specified date argument. The parametr may be any date datatype.

SELECT  DAY( '2016-8-24' ), DAY( '2016/8/25' )
       ,DAY( '20160826' ), DAY( '2016.08.27' );

qw1

SELECT  DAY( SYSDATETIME() ), DAY( SYSDATETIMEOFFSET() ), DAY( SYSUTCDATETIME() );
       ,DAY( CURRENT_TIMESTAMP ), DAY( GETDATE() ), DAY( GETUTCDATE() ) ;

qw2
Parameter may be either number. It is day  beginning from January 1, 1900. Below it is example with number as parameter. This number denotes number of days.

SELECT  DAY(0) as '0 days', DAY(5) as '5 days', DAY(30) as '30 days'
       ,DAY(31) as '31 days', DAY(300) as '300 days';

aa1

Another function is MONTH(date) that returns an integer representing month of the year of the specified date argument. The parametr may be any date datatype.

SELECT  MONTH( '2016-8-24' ), MONTH( '2016/8/25' )
       ,MONTH( '20160826' ), MONTH( '2016.08.27' );

qw5

SELECT  MONTH( SYSDATETIME() ), MONTH( SYSDATETIMEOFFSET() ), MONTH( SYSUTCDATETIME() )
       ,MONTH( CURRENT_TIMESTAMP ), MONTH( GETDATE() ), MONTH( GETUTCDATE() ) ;

qw6
Parameter may be either number. It is month  beginning from January 1, 1900. Below it is example with number as parameter. This number denotes number of days.

SELECT MONTH(0) as 'month for 0 days', MONTH(547) as 'month for 547 days'
      ,MONTH(311) as 'month for 311 days';

aa2

Similar function is YEAR(date) that returns an integer representing year of the specified date argument. The parametr may be any date datatype.

SELECT  YEAR( '2016-8-24' ), YEAR( '2016/8/25' )
       ,YEAR( '20160826' ), YEAR( '2016.08.27' );

qw11

SELECT  YEAR( SYSDATETIME() ), YEAR( SYSDATETIMEOFFSET() ), YEAR( SYSUTCDATETIME() )
       ,YEAR( CURRENT_TIMESTAMP ), YEAR( GETDATE() ), YEAR( GETUTCDATE() ) ;

qw12Parameter may be either number. It is month  beginning from January 1, 1900. Below it is example with number as parameter. This number denotes number of days.

SELECT YEAR(0) as 'year for 0 days', YEAR(1111) as 'year for 1111 days'
      ,YEAR(3211) as 'year for 3211 days';

aa3

T-SQL delivers either functions: DATENAME (datepart, date) and DATEPART (datepart, date). First return string( nvarchar datatype ) representing the specified datepart of the specified date, second integer representing the specified datepart of the specified date.

The date parameter these both functions  may be any date datatype. The datepart parameter may be one of the following values and its abbreviates:

year	    yy, yyyy
quarter	    qq, q
month	    mm, m
dayofyear   dy, y
day	    dd, d
week	    wk, ww
weekday	    dw, w
hour	    hh
minute	    mi, n
second	    ss, s
millisecond ms
microsecond mcs
nanosecond  ns
TZoffset    tz

Below I present several examples using these functions:

SELECT  DATENAME( q, SYSDATETIME() );

dd1

SELECT  DATENAME( m, SYSDATETIME() ), DATEPART( m, SYSDATETIME() );

dd2

SELECT  DATENAME( weekday, SYSDATETIME() ), DATEPART( weekday, SYSDATETIME() );

dd3

SELECT  DATENAME( tz, SYSDATETIME() ), DATEPART( tz, SYSDATETIME() );

dd4

SELECT  DATENAME( yyyy, SYSDATETIME() ), DATEPART( yy, SYSDATETIME() );

dd5In most cases the return values will be the same.