Major enhancement in this front is new Date and Time datatypes and related functions.
Here Overview of the new Date and time datatypes
(a) Date : Date only which can store date raging from 0001-01-01 through 9999-12-31 ie. January 1, 1 A.D. through December 31, 9999 A.D. The storage size is 3 byte and Date datatype gives One day accuracy.
(b) Datetime2 : Its an extension to existing Datetime datatype and datetime2 can have larger range and accuracy. Existing Datetime range limitation is January 1, 1753, through December 31, 9999. Datetime2 provides range from 0001-01-01 through 9999-12-31. The storage size is 8 byte for both datetime2 and datetime. For all new developments its recommended to use Datetime2 because is more portable and it gives more second precision.
(c) Time : Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock. This provides more accuracy in terms of time than existing datetime datatype. Time datatype ranges 00:00:00.0000000 through 23:59:59.9999999 where as existing datetime data type only gives 00:00:00 through 23:59:59.997. It also provides a feature in which user can specify the precision they wanted from 0 to 99 nanoseconds. The storage size is 5 bytes, fixed, is the default with the default of 100ns fractional second precision.
SELECT CAST(getdate() AS time) AS 'time' – Default
Result : 09:43:13.6230000
SELECT CAST(getdate() AS time(2)) AS 'time' – User specific 2 percision
Result : 09:43:57.69
In the above example, the figure within brackets (ie 2) specify what precision we want. By default (if we not specify) its 7.
(d) 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. datetimeoffset have three parts. Date part , time part and timeoffset part. Datetimeoffset ranges from 0001-01-01 through 9999-12-31 and it takes 10 byte for storage.
(e) Hierarchyid : I have not tried this yet, for now I just wanted to mention that there is a new system datatype called Hierarchyid in SQL Server 2008. Books online has very detailed section on this, I need to check that
Conclusion
These date and time datatypes are definitly mosuse wanted datatypes for long time. For any new development purpose use these new datatypes available. I need to explore more with these datatypes. Books online has very detailed documentation with examples which needs to be referred.
Here Overview of the new Date and time datatypes
(a) Date : Date only which can store date raging from 0001-01-01 through 9999-12-31 ie. January 1, 1 A.D. through December 31, 9999 A.D. The storage size is 3 byte and Date datatype gives One day accuracy.
(b) Datetime2 : Its an extension to existing Datetime datatype and datetime2 can have larger range and accuracy. Existing Datetime range limitation is January 1, 1753, through December 31, 9999. Datetime2 provides range from 0001-01-01 through 9999-12-31. The storage size is 8 byte for both datetime2 and datetime. For all new developments its recommended to use Datetime2 because is more portable and it gives more second precision.
(c) Time : Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock. This provides more accuracy in terms of time than existing datetime datatype. Time datatype ranges 00:00:00.0000000 through 23:59:59.9999999 where as existing datetime data type only gives 00:00:00 through 23:59:59.997. It also provides a feature in which user can specify the precision they wanted from 0 to 99 nanoseconds. The storage size is 5 bytes, fixed, is the default with the default of 100ns fractional second precision.
SELECT CAST(getdate() AS time) AS 'time' – Default
Result : 09:43:13.6230000
SELECT CAST(getdate() AS time(2)) AS 'time' – User specific 2 percision
Result : 09:43:57.69
In the above example, the figure within brackets (ie 2) specify what precision we want. By default (if we not specify) its 7.
(d) 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. datetimeoffset have three parts. Date part , time part and timeoffset part. Datetimeoffset ranges from 0001-01-01 through 9999-12-31 and it takes 10 byte for storage.
(e) Hierarchyid : I have not tried this yet, for now I just wanted to mention that there is a new system datatype called Hierarchyid in SQL Server 2008. Books online has very detailed section on this, I need to check that
Conclusion
These date and time datatypes are definitly mosuse wanted datatypes for long time. For any new development purpose use these new datatypes available. I need to explore more with these datatypes. Books online has very detailed documentation with examples which needs to be referred.
What data type allows a 24-hour clock, please?
ReplyDeleteIn Time Data Type you can store time in 24 hour format.
ReplyDeleteOr
You can extract time from DATETIME or SMALLDATETIME Data Type using CONVERT Function.