Q
Problem solve Get help with specific problems with your technologies, process and projects.

Getting the exact time from a query

If you're hoping to get seconds accuracy from a query using smalldatetime, you're out of luck. Expert Adam Mechanic offers another solution in this expert response.

I am using a dynamic SQL query in a stored procedure since the table name varies. Part of the query converts a smalldatetime to VARCHAR (since the dynamic SQL query is in a string), but zeroes out the seconds. I need the exact seconds. How do I get around this?
First of all, I would be remiss in my duties as a SQL Server educator if I didn't chastise you for passing in a dynamic table name to a stored procedure! Doing so tightly couples your application code to your database code. This means that any change in your database (i.e. a change in table names) will require that you also change your application. That is not a good situation to be in. Databases, I believe, do their best jobs as centralized, black-box storage mechanisms that can be shared amongst multiple applications, without code change in every application with every database change. As for the smalldatetime, I'm afraid you're out of luck. That datatype is only accurate to the minute. For second (and three millisecond) accuracy, you'll have to use datetime.


Do you have comments on this Ask the Expert Q&A? Let us know.
This was last published in August 2005

Dig Deeper on SQL Server Stored Procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close