SQL Server’s TIMESTAMP Is Not What You May Think It Is.

TIMESTAMP vs. DATETIME: What’s the diff?

The TIMESTAMP data type in SQL Server is not what you may think it is.

I know right?

The general consensus seems to lean towards it just being a bad naming decision by someone at Microsoft.

The TIMESTAMP in SQL Server is actually a row version that is automatically generated and, if used properly, guaranteed to be unique.

How do you use it improperly? Well, MSDN indicates it can be cheated to be non-unique by using a SELECT…INTO statement.

Now, also keep in mind that TIMESTAMP in SQL Server is deprecated and ROWVERSION should be used instead.

But what if you are trying to store a date or time stamp in the database?

 

Great, So Now What Do I Do?

You are in luck! There are a few ways you can capture current date and time information in SQL Server.

In SQL Server, the GETDATE() function will return the current server /system timestamp as a DATETIME value with no time zone offset.

There are optional SYSDATETIME(), SYSUTCDATETIME(), and SYSDATETIMEOFFSET functions that can be used for more precision. It will also capture the current ime to about 4 decimal places.

SYSDATETIME() produces more fractional seconds than GETDATE(). In fact it captures up to 7 decimal places after the second value.

That is getting pretty precise!

SYSUTCDATETIME() produces the same fractional seconds as SYSDATETIME() but returns the UTC time rather than local time.

SYSDATETIMEOFFSET() will return the same fractional seconds as SYSDATETIME and SYSUTCDATETIME, but adds the time zone offset as well.

 

You can see the different results from each of these functions on your server by running the following script:

SELECT  GETDATE() AS GET_DATE ,
        SYSDATETIME() AS SYS_DATETIME ,
        SYSUTCDATETIME() AS SYS_UTC_DATETIME ,
        SYSDATETIMEOFFSET() AS SYS_DATETIME_OFFSET

 

 

[divider_top][icon_box icon=”users” title=”Who is Chris Bell?”]

Chris Bell is an independent SQL Server consultant, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com. Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services, training and the FREE sp_WOxCompliant compliance check script for your SQL Server environment.

Click here to contact Chris or to follow him on twitter. [/icon_box]