Date & Time Assignment

DECLARE @Date DATE = '2012-10-25';
DECLARE @DateTime DATETIME = '2007-05-08 12:35:29.123';
SELECT 
	@Date AS 'DATE', 
	@DateTime AS 'DATETIME';

DECLARE @Time1 TIME = '12:34:54.1234';
DECLARE @Time2 TIME(4) = '12:34:54.1234';
DECLARE @Time3 TIME(0) = @Time2;
SELECT 
	@Time1 AS 'TIME', 
	@Time2 AS 'TIME(4)', 
	@Time3 AS 'TIME(0)';

DECLARE @SMALLDATETIME SMALLDATETIME = '1955-12-13 12:43:10';
SELECT @SMALLDATETIME AS [SMALLDATETIME];

DATETIMEOFFSET

• Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

DECLARE @DateTimeOffset DATETIMEOFFSET(4) = '12-10-25 12:32:10 +01:0';

DATETIME2

• Defines a date that is combined with a time of day that is based on the 24-hour clock.
• datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.
• Date range: 0001-01-01 through 9999-12-31
• Storage size: 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4.
• All other precisions require 8 bytes.

DECLARE @DATETIME2 DATETIME2(4) = '12-10-25 12:32:10.1234';

DATEPART

• Returns an integer that represents the specified datepart of the specified date.
• SYSDATETIME returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
• SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE.
• SYSDATETIMEOFFSET includes the system time zone offset.

SELECT 
	GETDATE() AS 'DATE',
	YEAR(GETDATE()) AS 'YEAR()', 
	MONTH(GETDATE()) AS 'MONTH()',
	DAY(GETDATE()) AS 'DAY()',
	DATEPART(HOUR, GETDATE()) AS 'DATEPART(HOUR, @DateTime)',
	DATEPART(MINUTE, GETDATE()) AS 'DATEPART(MINUTE, @DateTime)',
	DATEPART(SECOND, GETDATE()) AS 'DATEPART(SECOND, @DateTime)';

DATENAME

• Returns a character string that represents the specified datepart of the specified date.