Change Date Time Fields to Support Second-Level Precision

This article refers to EQuIS Databases in Microsoft SQL Server.

Many tables in the EQuIS Database include columns for date and time data. Microsoft SQL Server supports these data types for date and time data:

•datetime with the following properties:

o6 bytes per value stored

oRange of 1753-01-01 (January 1, 1753) through 9999-12-31 (December 31, 9999)

oAccuracy to 1/100 of a second

oFor more information, see Microsoft's datetime (Transact-SQL) article

•smalldatetime with the following properties:

o4 bytes per value stored

oRange of 1900-01-01 (January 1, 1900) through 2079-06-06 (June 6, 2079)

oAccuracy to the minute

oFor more information, see Microsoft's smalldatetime (Transact-SQL) article

By default, the EQuIS Database uses smalldatetime because it is more efficient and meets most needs. In some situations, users need additional levels of precision. Any date or time field in EQuIS may be changed from smalldatetime to datetime to enable data storage with second-level precision.

Change Date and/or Time Data Types

1.Start SQL Server Management Studio.

2.Log into and select the desired database.

3.Select New Query.

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 this script for DT_SAMPLE:

alter table DT_SAMPLE
alter column SAMPLE_DATE datetime

5.Click Execute.

Note: Database owner or administrator privileges are required in order to make database changes. Always backup the entire database before making any changes.

Troubleshooting

Attempting to modify the data type 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.

Remove the index and/or constraint to make the change, 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 TestAlternateKey
   if exists (select name from sysindexes where name = 'TestAlternateKey')
     alter table [dbo].[dt_test] drop constraint TestAlternateKey
   go

   -- change analysis_date to datetime
   alter table [dbo].[dt_test] alter column analysis_date datetime null
   go

   -- restore TestAlternateKey
   -- NOTE:  comment any fields that are not included in your TestAlternateKey
   alter table [dbo].[dt_test] add  constraint TestAlternateKey unique nonclustered
   (
    [facility_id]
   ,[sample_id]
   ,[analytic_method]
   ,[analysis_date]
   ,[test_type]
   ,[fraction]

   ,[column_number]
   )
   go

Note: The auto-populate feature will populate the system date when it is a required column.