SQL SERVER – Difference Between CURRENT_TIMESTAMP and GETDATE() – CURRENT_TIMESTAMP Equivalent in SQL Server

A common question – I often get from Oracle/MySQL Professionals:

“What is the Equivalent to CURRENT_TIMESTAMP in SQL Server?”

Here is a common question I often get from SQL Server Professionals:

“What are differences between Difference Between CURRENT_TIMESTAMP and GETDATE ()?”

Very simple question but have showed up so frequently that I feel like to write about it.

Solarwinds

Well in SQL Server GETDATE() is Equivalent to CURRENT_TIMESTAMP. However, if you use CURRENT_TIMESTAMP in your select statement it will work fine.

SQL SERVER - Difference Between CURRENT_TIMESTAMP and GETDATE() - CURRENT_TIMESTAMP Equivalent in SQL Server gdv

You can see in the above example – both of them returns the same value. Now let us go to next question regarding difference between GETDATE and CURRENT_TIMESTAMP. Well, the matter of the fact, there is no difference between them in SQL Server (Reference Link). CURRENT_TIMESTAMP is an ANSI SQL function, whereas GETDATE is T-SQL implementation of the same function. Both of them derive value from the operating system of the computer on which SQL Server instance is running.

Above discussion prompts another question – in this case, what should one use GETDATE or CURRENT_TIMESTAMP?

Well, this is indeed tricky and interesting question. I think I am very comfortable using the GETDATE () so I will go to use it but a matter of the fact there is no right or wrong answer. If you want to follow ancient saying “When in Rome, do as the Romans do”, I suggest using the GETDATE (), or continue using CURRENT_TIMESTAMP.

With that said, there is one very important property we all need to keep in mind. If you use CURRENT_TIMESTAMP while creating an object, they are automatically converted to GETDATE() and stored internally. To illustrate what I am suggesting here is the example –

Create a table using the following script

CREATE TABLE [dbo].[TestTable](
[Cold2] [datetime] NULL
)
ON [PRIMARY] GO
ALTER TABLE [dbo].[TestTable] ADD DEFAULT (CURRENT_TIMESTAMP) FOR [Cold2] GO

Now go to SSMS and generate the script for the table and you will notice following syntax.

CREATE TABLE [dbo].[TestTable](
[Cold2] [datetime] NULL
)
ON [PRIMARY] GO
ALTER TABLE [dbo].[TestTable] ADD DEFAULT (GETDATE()) FOR [Cold2] GO

You can notice that SQL Server have automatically converted CURRENT_TIMESTAMP to GETDATE(). I guess this gives us an idea how they behave. Now go ahead and make your choice! Do let me know which one will you use CURRENT_TIMESTAMP or GETDATE () in the comments area.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video
Next Post
SQLAuthority News – Chrome Browser – Personal Technology

Related Posts

26 Comments. Leave new

Leave a Reply

Menu