As new systems are introduced to your organization, there will be instances that you would need to go back to a transaction that occurred prior to the new system implementation. A past transaction, which has a bearing on the accounting reports or production, should be included in the current system database. To do so, the SQL Server date needs to be changed in order to insert the transaction and make such work within the current system.
SQL Server allows designers to work easily with time zones, dates without times and dates in ancient history and the future. It has different date/time columns and these are:
1. DATETIME. Where variables store 8-byte time and date values ranging from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds.
2. DATETIME2. Where variables use are between 6-8 bytes to store dates and times between January 1, 0001 and December 31, 9999 with an accuracy of 100 nanoseconds.
3. SMALLDATETIME. Where variables store 4-byte time and date values ranging from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute.
4. DATE. Where variables use 3 bytes to store a date only with no time information in the range January 1, 0001 through December 31, 9999.
5. TIME. Where variables use between 3-5 bytes to store a time only with no date information to an accuracy of 100 nanoseconds.
6. DATETIMEOFFSET. Where variables store the date and time using between 8-10 bytes. The values stored would be the same as the values stored under the datetime2 datatype in addition to the time zone offset.
7. TIMESTAMP. Where variables are automatically populated by SQL Server with the time that a row is created or modified. The timestamp value is based on the internal clock and does not correspond to real time.