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

  • GETDATE() – using it since I learned about GETDATE()
    No need to use Current_Timestamp as per your suggestion.

    Reply
  • Vinay B Hiraskar
    December 20, 2012 10:46 am

    Well, after a very good Discussion I felt GETDATE() is Better for me since i am much familiar with that… Any How as in Discussion CURRENT_TIMESTAMP automatically converted to GETDATE().

    Reply
  • When Both Mean the Same …. Then What’s The Purpose of Current_Timestamp when getdate() is already there..

    Reply
  • GetDate() is the one I will live me throughout my life

    Reply
  • 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()
    :)

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

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

    Reply
  • Good example, i will go with ur suggesion

    Reply
  • 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.

    Reply
  • Interesting; so there’s no performance advantage then (I suppose outside of typing getdate() being shorter in typing length)?

    Reply
  • nakulvachhrajani
    January 1, 2013 12:32 am

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

    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,

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

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

      Reply
    • SELECT A.empid, sum(datediff(mi,a.checktime,b.checktime))/60
      FROM
      (
      SELECT empid, checktime, ROW_NUMBER() over (partition by empid order by checktime) as row_cnt
      FROM dbo.EmpMaster
      WHERE chektype = 1
      )A
      INNER JOIN
      (
      SELECT empid, checktime, ROW_NUMBER() over (partition by empid order by checktime) as row_cnt
      FROM dbo.EmpMaster
      WHERE chektype = 2
      )B ON A.empid = B.empid and a.row_cnt = b.row_cnt
      group by a.empid

      Reply
    • begin
      declare @start datetime ,@ends datetime
      set @start=(select MIN(checktime) from EmpMaster where chektype=1)
      set @ends=(select Max(checktime) from EmpMaster where chektype=2)
      select @start,@ends
      select DATEDIFF(hh,@start,@ends)
      end

      Reply
  • select empid, count(chektype)
    from EmpMaster
    where chektype = 1
    group by empid;

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

    Reply
  • Current_timestamp is ANSI standard
    and Getdate() is T-SQL implementation
    is only the difference between these two.

    Reply
  • CURRENT_TIMESTAMP, yes , I am using always it

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

    Reply
  • 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()

    Reply
  • Eldon Tenorio
    June 19, 2014 1:22 pm

    I have always used GetDate().

    Reply
  • Good analysis with right example

    Reply

Leave a Reply

Menu