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' );
SELECT DAY( SYSDATETIME() ), DAY( SYSDATETIMEOFFSET() ), DAY( SYSUTCDATETIME() ); ,DAY( CURRENT_TIMESTAMP ), DAY( GETDATE() ), DAY( GETUTCDATE() ) ;
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';
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' );
SELECT MONTH( SYSDATETIME() ), MONTH( SYSDATETIMEOFFSET() ), MONTH( SYSUTCDATETIME() ) ,MONTH( CURRENT_TIMESTAMP ), MONTH( GETDATE() ), MONTH( GETUTCDATE() ) ;
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';
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' );
SELECT YEAR( SYSDATETIME() ), YEAR( SYSDATETIMEOFFSET() ), YEAR( SYSUTCDATETIME() ) ,YEAR( CURRENT_TIMESTAMP ), YEAR( GETDATE() ), YEAR( GETUTCDATE() ) ;
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 YEAR(0) as 'year for 0 days', YEAR(1111) as 'year for 1111 days' ,YEAR(3211) as 'year for 3211 days';
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() );
SELECT DATENAME( m, SYSDATETIME() ), DATEPART( m, SYSDATETIME() );
SELECT DATENAME( weekday, SYSDATETIME() ), DATEPART( weekday, SYSDATETIME() );
SELECT DATENAME( tz, SYSDATETIME() ), DATEPART( tz, SYSDATETIME() );
SELECT DATENAME( yyyy, SYSDATETIME() ), DATEPART( yy, SYSDATETIME() );