SQL SERVER – Difference Between CURRENT_TIMESTAMP and GETDATE() – CURRENT_TIMESTAMP Equivalent in SQL Server
- by pinaldave
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
