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.

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

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 (http://blog.sqlauthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology