Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 1102 | Views in the last 30 days: 1
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


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

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


Column Store vs Row Store Indexes

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

date manipulation    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones