(*) For SQL Server 2008 and newer.

(**) Not supported in Sybase ASE / SQL Anywhere

Note that older TDS protocol versions do report not identify date data types correctly. Use ':type=date' notation in column name to specify the correct data type.

declare @d date
declare @dt datetime2
declare @time time
declare @datetimeoffset datetimeoffset


select @d = getdate(), @dt = getdate(), @time = getdate(), @datetimeoffset = getdate()

select  @d as 'date:type=date', 
  @dt as 'datetime2:type=datetime', 
  @time as 'time:type=time', 
  @datetimeoffset as 'datetimeoffset:type=datetime'