The Current Date

• Jeff Moden

  SSC Guru

  Points: 1004323

  More actions

  August 10, 2017 at 7:48 pm

  #1954907

  Steve Jones - SSC Editor - Thursday, August 10, 2017 5:38 PM

  GETDATE() can also be much faster at scale. If you're just looking for days/hours/minutes/seconds, why not use datetime?

  No one would like to prove that to be true more than I... but I'm not seeing it to be true.  Do you have an example of what you're speaking of because I'm not seeing it in the following simple test.

  
CREATE TABLE #DT (DT DATETIME);
CREATE TABLE #DT2 (DT2 DATETIME2(3));
SET STATISTICS TIME ON;
GO
INSERT INTO #DT(DT)
SELECT TOP 10000000
        GETDATE()
   FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
GO
INSERT INTO #DT2(DT2)
SELECT TOP 10000000
        SYSDATETIME()
   FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
GO
DROP TABLE #DT,#DT2
;


  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736173

  More actions

  August 10, 2017 at 9:35 pm

  #1954909

  I saw a test, but can't find it now, nor am I allowed since I only had a few minutes before everyone woke up. Now that I think of it, perhaps the issue was conversion of datetime2 -> datetime as many functions use this? Not sure, but I was surprised to see someone show a datetime2 being slower than datetime in a situation.