Changing Date Time Fields to Support Second-Level Precision
Copyright © 2018 EarthSoft, Inc • Modified: 12 Dec 2018
This article refers to EQuIS Databases in Microsoft SQL Server.
Many tables in the EQuIS Database include columns that store date and time data. Microsoft SQL Server supports two data types for date and time data.
•datetime: 8 bytes, January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth (1/300) of a second, which provides greater accuracy.
•smalldatetime: 4 bytes, January 1, 1900 through June 6, 2079, with accuracy to the minute, which provides greater efficiency.
By default, the EQuIS SQL Server Database uses smalldatetime, because it is more efficient and meets the needs of most uses. In some situations, additional levels of precision are needed. Any date or time field in EQuIS may be changed from smalldatetime to datetime to enable data storage with second-level precision.
1.Start SQL Server Management Studio.
2.Select the desired database.
3.Select New Query.
4.Paste the following script in the SQLQuery window, with the appropriate table name and column name.
alter table <table_name>
alter column <column_name> datetime
For example, see the script below for DT_SAMPLE.
alter table DT_SAMPLE
alter column SAMPLE_DATE datetime
Note: Database owner or administrator privileges are required in order to make database changes. Always backup the entire database before making any changes.
Attempting to modify the datatype of a column that is part of an index or constraint may fail as follows:
ALTER TABLE ALTER COLUMN xxxxxx failed because one or more objects access this column.
Successfully applying such a modification requires the user to first remove the index and/or constraint, and then restore it after the column has been modified. For example, suppose the user wants to modify DT_TEST.ANALYSIS_DATE to be datetime. If ANALYSIS_DATE is part of the TestAlternateKey, then drop the TestAlternateKey constraint and recreate it as follows:
-- drop the existing TestAlernateKey
if exists (select name from sysindexes where name = 'TestAlternateKey')
alter table [dbo].[dt_test] drop constraint TestAlternateKey
-- change analysis_date to datetime
alter table [dbo].[dt_test] alter column analysis_date datetime null
-- restore TestAlternateKey
-- NOTE: comment any fields that are not included in your TestAlternateKey
alter table [dbo].[dt_test] add constraint TestAlternateKey unique nonclustered
Note: The auto-populate feature has been modified to populate the system date when it is a required column.