New Date Time data types & function in SQL Server 2008
In SQL Server 2008 Microsoft has introduced new date time data types and functions to help developers. Till SQL 2005 if you need to declare a column as date or time alone you need to use DATETIME data type which inputs both date and time and developers needs to convert or cast function to bring date or time alone. As you know using convert in a large column will slightly affect performance. Considering all these factors MS added the following data types in SQL 2008. Lets discuss on this
Data Types
Time:
This data type will allow you to insert only time, even if the input value contains date it will take only time. Precision is applicable to this data type to get NANO seconds. Maximum and default precision value is 7. Sample is shown in the image below and the precision value is the table
Date:
This data type will allow you to insert only date, even if the input value contains time it will take only date.
Datetime2:
This new data type will allow users to enter \ capture nano seconds value in output. Sample is shown below
Datetimeoffset:
This data type allows you to use off set value inside the date time value itself. Precision is applicable to this data type to get NANO seconds. Maximum precision value is 7. Sample data shown, here you can find offset value 5:30 is detected and shown.
Existing Data Time Data Types:
SQL Server 2008 supports existing date time data types “Datetime & smalldatetime” and hence we are not going deep into this data types.
Date Time Functions:
SYSDATETIME
This function is similar to GETDATE function, only difference is that this will return NANO seconds value too in the output. Sample data is shown below
SYSDATETIMEOFFSET
This function is similar to SYSDATETIME function however this will include offset value in the output. Offset value is fetched from system date. Sample data is shown below
SYSUTCDATETIME
This function is similar to GETUTCDATE function expect that this function will include NANO seconds value too in the output. Sample data is shown below
CURRENT_TIMESTAMP
Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running
SWITCHOFFSET
Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.
TODATETIMEOFFSET
Returns a datetimeoffset value that is translated from a datetime2 expression.
Existing Data Time Functions:
SQL Server 2008 supports existing date time functions shown below.
DATEADD
DATEDIFF
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
MONTH
YEAR
ISDATE
Conclusion:
New date time data types and functions in SQL Server 2008 allows developers to have a segregated date and time values.