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.

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)

About these ads

21 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()
    :)

  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).

  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….

  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.

  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,

  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

    • 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!

  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!

  8. I love you, Man! When I ask google a SQL question, click, then see your face, I know I’m in the right place. Keep up the good work!

  9. 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()

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