SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

datetime precision

By William Talada,

There is a problem with storing too much precision in a column. For example, storing $12.345 as money does not make sense in the U.S. since we only use two decimals for cents. The same problem occurs with datetimes and smalldatetimes. We sometimes store time with a date when, for example, a price is always effective for whole days only. Encountering time data in that database column will completely confuse developers and introduce bugs when they write sql for it.

I've included a function that trims a getdate() value to whatever precision a table's column should store. You can use the function in a rule on that column too, to guarantee a higher precision is not stored. It would be nice if Microsoft considered adding a precision to the datetime type declaration as they do for decimal(precision,scale).



Total article views: 1139 | Views in the last 30 days: 2
Related Articles

The output column "......." has a precision that is not valid. The precision must be between 1 and 38.

Oracle PLEDB Source and MSSQL destination. "The output column "......." has a precision that is not ...


Issue with a Decimal column in a table

Limitation with Decimal column precision and scale


Column Store vs Row Store Indexes

How does a column store index compare to a (traditional)row store index with regards to performance


How do I, in effect, SELECT FROM a stored procedure

SELECT COLUMN_NAME, TYPE_NAME, PRECISION FROM(EXEC sp_columns_90 @table_name = 'foo')

date manipulation