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.

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

gdv SQL SERVER   Difference Between CURRENT TIMESTAMP and GETDATE()   CURRENT TIMESTAMP Equivalent in SQL Server

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)

23 thoughts on “SQL SERVER – Difference Between CURRENT_TIMESTAMP and GETDATE() – CURRENT_TIMESTAMP Equivalent in SQL Server

  1. when ever possible we should have to follow ANSI standard,
    because Its easy to migrate database from sql server to oracle or others.

    I was using (+) for join in oracle but one big database have taken my too much time because of these type of non ANSI code.
    so right now i am trying to use ANSI standard when ever possible
    like,
    coalesce instead of isnull()/nvl()
    inner/outer join instead of (+)/(*) etc…
    now I got one another point current_timestamp instead of getdate()
    :)

    Like

  2. I have always used getdate(), ever since I learned you could use that to define a field that defaults to the current date/time. It’s also easier to type (being all on the left hand, on the top two rows, and lowercase). It’s also slightly clearer to me what it will do (though I know they do the same thing).

    Like

  3. Hi Pinal Dave..
    How can i know Sqlserver is ready or not…
    i.e while i am creating connection to sql server if sql server is not ready we will get exception… so first i want to know is sql server is ready to accept request or not… how to know it….

    Like

  4. A good SQL progammer will always write ANSI/ISO Standard code. The Standard also includes CURRENT_DATE while T-SQL needs CAST (CURRENT_TIMESTAMP AS DATE) instead.

    That rewrite of code from Standard to dialect needs to be corrected by Microsoft as soon as possible. Everytime I see getdate() I have flashbacks to the 1970’s and UNIX on a 16-bit minicomputer.

    Like

  5. I would prefer going the ANSI standard way, i.e. with CURENT_TIMESTAMP.

    Personally I switched from using CONVERT to CAST although MS SQL Server internally transforms a CAST into a CONVERT (http://beyondrelational.com/modules/2/blogs/77/posts/11334/cast-vs-convert-is-there-a-difference-as-far-as-sql-server-is-concerned-which-is-better.aspx).

    The reason I go with ANSI standard is because the standard is “constant”, meaning the code written in the standard is ensured to work as expected & designed for untill a newer version of the standard is released,

    Like

  6. Please help me to find a solution for this problem

    I have a database that stores login/logout time for a particular employee.
    Create table EmpMaster
    (
    empid int,
    checktime datetime,
    chektype int // 1-checkin 2-checkout
    )
    Insert into EmpMaster values(2,’2013-01-03 9:30:00.00′,1)
    Insert into EmpMaster values(2,’2013-01-03 10:30:00.00′,2)
    Insert into EmpMaster values(2,’2013-01-03 11:30:00.00′,1)
    Insert into EmpMaster values(2,’2013-01-03 12:00:00.00′,2)
    Insert into EmpMaster values(2,’2013-01-03 14:30:00.00′,1)
    Insert into EmpMaster values(2,’2013-01-03 16:00:00.00′,2)

    i want to find out how many hours a particular employee was checked in

    eg
    empid hours
    2 7

    Like

    • Hi Thejus,
      Script for your query is as below –
      ————————————————————————————————
      SELECT A.empid, DATEDIFF(hh,A.checktime, B.checktime) WorkHrs
      FROM
      (
      SELECT empid, MIN(checktime) checktime FROM dbo.EmpMaster
      WHERE chektype = 1
      GROUP BY empid
      )A
      INNER JOIN
      (
      SELECT empid, MAX(checktime) checktime FROM dbo.EmpMaster
      WHERE chektype = 2
      GROUP BY empid
      )B ON A.empid = B.empid
      ————————————————————————————————

      Please check and revert!

      Like

  7. Hi Thejus,
    Script for your query is as below –
    ————————————————————————————————
    SELECT A.empid, DATEDIFF(hh,A.checktime, B.checktime) WorkHrs
    FROM
    (
    SELECT empid, MIN(checktime) checktime FROM dbo.EmpMaster
    WHERE chektype = 1
    GROUP BY empid
    )A
    INNER JOIN
    (
    SELECT empid, MAX(checktime) checktime FROM dbo.EmpMaster
    WHERE chektype = 2
    GROUP BY empid
    )B ON A.empid = B.empid
    ————————————————————————————————

    Please check and revert!

    Like

  8. I’ve always used GETDATE() and had to read up on CURRENT_TIMESTAMP because a C# Developer used it when writing some SQL.
    My Vote is for GETDATE()

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s