GETDATE() or CURRENT_TIMESTAMP?

  • Hello,

    Can anyone please explain the difference between GETDATE() and CURRENT_TIMESTAMP ?

    I'm sure there must be, but I am not certain what it is and where each is best used.

    Any help appreciated.

    Regards

    Steve

  • There no performance difference between the two.

    CURRENT_TIMESTAMP is the ANSI SQL equivalent to GETDATE().

    There's post discussing this which you may find useful.

    http://www.sqlservercentral.com/Forums/Topic991342-373-1.aspx

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • CURRENT_TIMESTAMP is the ANSI standard method for getting date & time while GETDATE() is the T-SQL method.

    Also GETDATE() returns the datetime specific to the database including the database time zone offset where as CURRENT_TIMESTAMP doesn't include the database offset.

    Edit: No difference in terms of performance.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (5/1/2012)


    Also GETDATE() returns the datetime specific to the database including the database time zone offset where as CURRENT_TIMESTAMP doesn't include the database offset.

    What do you mean by that?

    Getdate and current_timestamp return the same thing, the current time on the database server, regardless of what timezone the server is set to. It's GETUTCDATE() that returns the GMT (UTC) time and SYSDATETIMEOFFSET that returns the time with the timezone offset information

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (5/1/2012)


    vinu512 (5/1/2012)


    Also GETDATE() returns the datetime specific to the database including the database time zone offset where as CURRENT_TIMESTAMP doesn't include the database offset.

    What do you mean by that?

    Getdate and current_timestamp return the same thing, the current time on the database server, regardless of what timezone the server is set to. It's GETUTCDATE() that returns the GMT (UTC) time and SYSDATETIMEOFFSET that returns the time with the timezone offset information

    :blush::blush: uhmm!!....Sorry got myself tangled a bit with the various date versions.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • They do the same thing. Probably even use the exact same binaries behind the scenes where SQL Server does all the actual work.

    So use CURRENT_TIMESTAMP if you can. It's a good habit, in case GETDATE() goes the way of *= or any of a number of other non-ANSI T-SQL extensions. Since there's no advantage of one over the other in terms of actual use, CURRENT_TIMESTAMP has a slight edge in terms of future-proofing code. Very slight, but it exists.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I use SYSDATETIME().

    It is ANSI/ISO compliant (supposedly, I only found one reference at http://sqlhints.com/tag/datetime-vs-datetime2/).

    SYSDATETIME() adds a level of precision useful if I'm logging transactions and DATETIME2(n) datatype can use less storage space if you specify the precision appropriately. It's only available starting in SQL Server 2008 and can populate the DATETIME2 data type.

    If I need to work with and older database I use CURRENT_TIMESTAMP or, if the DBA doesn't like CURRENT_TIMESTAMP, I use GETDATE().

    SELECT

    SYSDATETIME() [SYSDATETIME],

    CURRENT_TIMESTAMP [CURRENT_TIMESTAMP],

    GETDATE() [GETDATE]

    Results:

    SYSDATETIME

    2014-10-01 10:46:09.8724461

    CURRENT_TIMESTAMP

    2014-10-01 10:46:09.870

    GETDATE

    2014-10-01 15:46:09.870

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply