Can't zero pad a variable

• ken.trock

  SSCertifiable

  Points: 5147

  More actions

  October 11, 2017 at 2:18 pm

  #329153

  For some reason I can't pad an early day of month with '0'. Not sure why. There are no mixtures of integer and character comparisons here. 

  Thanks

  
DECLARE @RunDate DATE = '10/06/17'
DECLARE @RunDay CHAR(2)
SET @RunDay = CASE WHEN DAY(@RunDate) < 10 THEN '0' + CAST(DAY(@RunDate) AS CHAR(1)) ELSE DAY(@RunDate) END
SELECT @RunDay   --Returns 6 for me, not 06


• Phil Parkin

  SSC Guru

  Points: 246977

  More actions

  October 11, 2017 at 2:25 pm

  #1963270

  ken.trock - Wednesday, October 11, 2017 2:18 PM

  For some reason I can't pad an early day of month with '0'. Not sure why. There are no mixtures of integer and character comparisons here. 

  Thanks

  
DECLARE @RunDate DATE = '10/06/17'
DECLARE @RunDay CHAR(2)
SET @RunDay = CASE WHEN DAY(@RunDate) < 10 THEN '0' + CAST(DAY(@RunDate) AS CHAR(1)) ELSE DAY(@RunDate) END
SELECT @RunDay   --Returns 6 for me, not 06


  Try this:
  DECLARE @RunDate DATE = '10/06/17';
DECLARE @RunDay CHAR(2);

  SET @RunDay = RIGHT('0' + CAST(DAY(@RunDate) AS VARCHAR(2)), 2);

  SELECT @RunDay;

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• JustMarie

  SSCertifiable

  Points: 7771

  More actions

  October 11, 2017 at 2:52 pm

  #1963276

  The reason you're not seeing your padding and Phil's code will work is that the date functions return integers. Prepending a zero to an integer does nothing for the output.

  Casting the results to varchar data types allows you to treat them as strings. See this one for the full case statement. If you're also doing month then you'll have to do a longer concatenation to pull it together but it's similar.

  DECLARE @RunDate DATE = '10/16/17';
  DECLARE @RunDay CHAR(2);

  SET @RunDay = CASE
  WHEN day(@RunDate) < 10
   THEN '0' + CAST(DAY(@RunDate) AS VARCHAR(2))
  ELSE cast(day(@RunDate) AS VARCHAR(2))
  END;

  SELECT @RunDay;

• Jeff Moden

  SSC Guru

  Points: 1004402

  More actions

  October 11, 2017 at 8:10 pm

  #1963297

  Phil Parkin - Wednesday, October 11, 2017 2:25 PM

  ken.trock - Wednesday, October 11, 2017 2:18 PM

  For some reason I can't pad an early day of month with '0'. Not sure why. There are no mixtures of integer and character comparisons here. 

  Thanks

  
DECLARE @RunDate DATE = '10/06/17'
DECLARE @RunDay CHAR(2)
SET @RunDay = CASE WHEN DAY(@RunDate) < 10 THEN '0' + CAST(DAY(@RunDate) AS CHAR(1)) ELSE DAY(@RunDate) END
SELECT @RunDay   --Returns 6 for me, not 06


  Try this:
  DECLARE @RunDate DATE = '10/06/17';
DECLARE @RunDay CHAR(2);

  SET @RunDay = RIGHT('0' + CAST(DAY(@RunDate) AS VARCHAR(2)), 2);

  SELECT @RunDay;

  You can cheat a bit here, Phil.  DATENAME can replace your CAST.

  
DECLARE  @RunDate DATE = '10/28/17'
        ,@RunDay  CHAR(2)
;
    SET @RunDay = RIGHT('0'+DATENAME(dd,@RunDate),2)
;
 SELECT @RunDay
;


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

• Phil Parkin

  SSC Guru

  Points: 246977

  More actions

  October 12, 2017 at 6:01 am

  #1963342

  Jeff Moden - Wednesday, October 11, 2017 8:10 PM

  You can cheat a bit here, Phil.  DATENAME can replace your CAST.

  
DECLARE  @RunDate DATE = '10/28/17'
        ,@RunDay  CHAR(2)
;
    SET @RunDay = RIGHT('0'+DATENAME(dd,@RunDate),2)
;
 SELECT @RunDay
;


  Nice refinement, Jeff.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 12, 2017 at 8:11 am

  #1963371

  Jeff Moden - Wednesday, October 11, 2017 8:10 PM

  Phil Parkin - Wednesday, October 11, 2017 2:25 PM

  ken.trock - Wednesday, October 11, 2017 2:18 PM

  For some reason I can't pad an early day of month with '0'. Not sure why. There are no mixtures of integer and character comparisons here. 

  Thanks

  
DECLARE @RunDate DATE = '10/06/17'
DECLARE @RunDay CHAR(2)
SET @RunDay = CASE WHEN DAY(@RunDate) < 10 THEN '0' + CAST(DAY(@RunDate) AS CHAR(1)) ELSE DAY(@RunDate) END
SELECT @RunDay   --Returns 6 for me, not 06


  Try this:
  DECLARE @RunDate DATE = '10/06/17';
DECLARE @RunDay CHAR(2);

  SET @RunDay = RIGHT('0' + CAST(DAY(@RunDate) AS VARCHAR(2)), 2);

  SELECT @RunDay;

  You can cheat a bit here, Phil.  DATENAME can replace your CAST.

  
DECLARE  @RunDate DATE = '10/28/17'
        ,@RunDay  CHAR(2)
;
    SET @RunDay = RIGHT('0'+DATENAME(dd,@RunDate),2)
;
 SELECT @RunDay
;


  I tend to cheat even more.
  
DECLARE @RunDate DATE = '10/02/17'
   ,@RunDay CHAR(2)
;
  SET @RunDay = RIGHT(100+DAY(@RunDate),2)
;
SELECT @RunDay
;


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Phil Parkin

  SSC Guru

  Points: 246977

  More actions

  October 12, 2017 at 8:18 am

  #1963372

  Luis Cazares - Thursday, October 12, 2017 8:11 AM

  I tend to cheat even more.
  
DECLARE @RunDate DATE = '10/02/17'
   ,@RunDay CHAR(2)
;
  SET @RunDay = RIGHT(100+DAY(@RunDate),2)
;
SELECT @RunDay
;


  Even better. This is turning into a challenge!

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Jeff Moden

  SSC Guru

  Points: 1004402

  More actions

  October 12, 2017 at 8:54 am

  #1963388

  Phil Parkin - Thursday, October 12, 2017 6:01 AM

  Jeff Moden - Wednesday, October 11, 2017 8:10 PM

  You can cheat a bit here, Phil.  DATENAME can replace your CAST.

  
DECLARE  @RunDate DATE = '10/28/17'
        ,@RunDay  CHAR(2)
;
    SET @RunDay = RIGHT('0'+DATENAME(dd,@RunDate),2)
;
 SELECT @RunDay
;


  Nice refinement, Jeff.

  Except I goofed. :blush:  I posted before testing.  My method turns out to be the slowest between yours, Luis', and mine.

  
--DROP TABLE #TestTable
GO
DECLARE  @StartDate DATE = '2000' --Inclusive
        ,@LimitDate DATE = '2017' --Exclusive
;
DECLARE  @Days      INT  = DATEDIFF(dd,@StartDate,@LimitDate)
;
 SELECT TOP 1000000
        SomeDate = DATEADD(dd,ABS(CHECKSUM(NEWID())%@Days),@StartDate)
   INTO #TestTable
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
GO
--===== PRECAST METHOD
  PRINT '========== PRECAST Method ==============================================================='
    SET STATISTICS TIME ON;
GO
DECLARE  @RunDay  CHAR(2);
 SELECT @RunDay = RIGHT('0' + CAST(DAY(SomeDate) AS VARCHAR(2)), 2)
   FROM #TestTable;
GO 3
    SET STATISTICS TIME OFF;
--===== DATENAME METHOD
  PRINT '========== DATENAME Method ==============================================================='
    SET STATISTICS TIME ON;
GO
DECLARE  @RunDay  CHAR(2);
 SELECT @RunDay = RIGHT('0'+DATENAME(dd,SomeDate),2)
   FROM #TestTable;
GO 3
    SET STATISTICS TIME OFF;
--===== DATENAME METHOD
  PRINT '========== MATH OVERRUN Method ==============================================================='
    SET STATISTICS TIME ON;
GO
DECLARE  @RunDay  CHAR(2);
 SELECT @RunDay = RIGHT(100+DAY(SomeDate),2)
   FROM #TestTable;
GO 3
    SET STATISTICS TIME OFF;


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

• Phil Parkin

  SSC Guru

  Points: 246977

  More actions

  October 12, 2017 at 9:04 am

  #1963391

  Jeff Moden - Thursday, October 12, 2017 8:54 AM

  Except I goofed. :blush:  I posted before testing.  My method turns out to be the slowest between yours, Luis', and mine.

  
--DROP TABLE #TestTable
GO
DECLARE  @StartDate DATE = '2000' --Inclusive
        ,@LimitDate DATE = '2017' --Exclusive
;
DECLARE  @Days      INT  = DATEDIFF(dd,@StartDate,@LimitDate)
;
 SELECT TOP 1000000
        SomeDate = DATEADD(dd,ABS(CHECKSUM(NEWID())%@Days),@StartDate)
   INTO #TestTable
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
GO
--===== PRECAST METHOD
  PRINT '========== PRECAST Method ==============================================================='
    SET STATISTICS TIME ON;
GO
DECLARE  @RunDay  CHAR(2);
 SELECT @RunDay = RIGHT('0' + CAST(DAY(SomeDate) AS VARCHAR(2)), 2)
   FROM #TestTable;
GO 3
    SET STATISTICS TIME OFF;
--===== DATENAME METHOD
  PRINT '========== DATENAME Method ==============================================================='
    SET STATISTICS TIME ON;
GO
DECLARE  @RunDay  CHAR(2);
 SELECT @RunDay = RIGHT('0'+DATENAME(dd,SomeDate),2)
   FROM #TestTable;
GO 3
    SET STATISTICS TIME OFF;
--===== DATENAME METHOD
  PRINT '========== MATH OVERRUN Method ==============================================================='
    SET STATISTICS TIME ON;
GO
DECLARE  @RunDay  CHAR(2);
 SELECT @RunDay = RIGHT(100+DAY(SomeDate),2)
   FROM #TestTable;
GO 3
    SET STATISTICS TIME OFF;


  That's not what I am seeing. On my system (2016 SP1), each method performs almost exactly the same.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• ken.trock

  SSCertifiable

  Points: 5147

  More actions

  October 12, 2017 at 9:08 am

  #1963393

  JustMarie - Wednesday, October 11, 2017 2:52 PM

  The reason you're not seeing your padding and Phil's code will work is that the date functions return integers. Prepending a zero to an integer does nothing for the output.

  Casting the results to varchar data types allows you to treat them as strings. See this one for the full case statement. If you're also doing month then you'll have to do a longer concatenation to pull it together but it's similar.

  DECLARE @RunDate DATE = '10/16/17';
  DECLARE @RunDay CHAR(2);

  SET @RunDay = CASE
  WHEN day(@RunDate) < 10
   THEN '0' + CAST(DAY(@RunDate) AS VARCHAR(2))
  ELSE cast(day(@RunDate) AS VARCHAR(2))
  END;

  SELECT @RunDay;

  Now that is interesting. All you're doing is Casting the Else clause as well. I thought I wasn't prepending a '0' to an integer but with the way that Case statement works, maybe I was 😎

  Ken

• ScottPletcher

  SSC Guru

  Points: 101118

  More actions

  October 12, 2017 at 3:28 pm

  #1963484

  I'd be inclined to just do this:

  
SELECT @RunDay = CONVERT(CHAR(2), SomeDate, 1)
FROM #TestTable;

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Jeff Moden

  SSC Guru

  Points: 1004402

  More actions

  October 12, 2017 at 11:41 pm

  #1963491

  Phil Parkin - Thursday, October 12, 2017 9:04 AM

  Jeff Moden - Thursday, October 12, 2017 8:54 AM

  Except I goofed. :blush:  I posted before testing.  My method turns out to be the slowest between yours, Luis', and mine.

  
--DROP TABLE #TestTable
GO
DECLARE  @StartDate DATE = '2000' --Inclusive
        ,@LimitDate DATE = '2017' --Exclusive
;
DECLARE  @Days      INT  = DATEDIFF(dd,@StartDate,@LimitDate)
;
 SELECT TOP 1000000
        SomeDate = DATEADD(dd,ABS(CHECKSUM(NEWID())%@Days),@StartDate)
   INTO #TestTable
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
GO
--===== PRECAST METHOD
  PRINT '========== PRECAST Method ==============================================================='
    SET STATISTICS TIME ON;
GO
DECLARE  @RunDay  CHAR(2);
 SELECT @RunDay = RIGHT('0' + CAST(DAY(SomeDate) AS VARCHAR(2)), 2)
   FROM #TestTable;
GO 3
    SET STATISTICS TIME OFF;
--===== DATENAME METHOD
  PRINT '========== DATENAME Method ==============================================================='
    SET STATISTICS TIME ON;
GO
DECLARE  @RunDay  CHAR(2);
 SELECT @RunDay = RIGHT('0'+DATENAME(dd,SomeDate),2)
   FROM #TestTable;
GO 3
    SET STATISTICS TIME OFF;
--===== DATENAME METHOD
  PRINT '========== MATH OVERRUN Method ==============================================================='
    SET STATISTICS TIME ON;
GO
DECLARE  @RunDay  CHAR(2);
 SELECT @RunDay = RIGHT(100+DAY(SomeDate),2)
   FROM #TestTable;
GO 3
    SET STATISTICS TIME OFF;


  That's not what I am seeing. On my system (2016 SP1), each method performs almost exactly the same.

  Here's the update code to include JustMarie's CASE method and Scott's CONVERT/CHAR(2) method.

  
DROP TABLE #TestTable
  GO
  DECLARE  @StartDate DATE = '2000' --Inclusive
          ,@LimitDate DATE = '2017' --Exclusive
  ;
  DECLARE  @Days      INT  = DATEDIFF(dd,@StartDate,@LimitDate)
  ;
   SELECT TOP 1000000
          SomeDate = DATEADD(dd,ABS(CHECKSUM(NEWID())%@Days),@StartDate)
     INTO #TestTable
     FROM      sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
  ;
  --===== PRECAST METHOD
    PRINT '========== PRECAST Method ==============================================================='
      SET STATISTICS TIME ON;
  GO
  DECLARE  @RunDay  CHAR(2);
   SELECT @RunDay = RIGHT('0' + CAST(DAY(SomeDate) AS VARCHAR(2)), 2)
     FROM #TestTable;
  GO 3
      SET STATISTICS TIME OFF;
  --===== DATENAME METHOD
    PRINT '========== DATENAME Method ==============================================================='
      SET STATISTICS TIME ON;
  GO
  DECLARE  @RunDay  CHAR(2);
   SELECT @RunDay = RIGHT('0'+DATENAME(dd,SomeDate),2)
     FROM #TestTable;
  GO 3
      SET STATISTICS TIME OFF;
  --===== DATENAME METHOD
    PRINT '========== MATH OVERRUN Method ==============================================================='
      SET STATISTICS TIME ON;
  GO
  DECLARE  @RunDay  CHAR(2);
   SELECT @RunDay = RIGHT(100+DAY(SomeDate),2)
     FROM #TestTable;
  GO 3
      SET STATISTICS TIME OFF;
  --===== CASE METHOD
    PRINT '========== CASE Method ==============================================================='
      SET STATISTICS TIME ON;
  GO
  DECLARE  @RunDay  CHAR(2);
   SELECT @RunDay =   CASE
                      WHEN day(SomeDate) < 10
                      THEN '0' + CAST(DAY(SomeDate) AS VARCHAR(2))
                      ELSE cast(day(SomeDate) AS VARCHAR(2))
                      END
     FROM #TestTable;
  ;

  GO 3
      SET STATISTICS TIME OFF;
  --===== CONVERT CHAR(2) METHOD
    PRINT '========== CONVERT CHAR(2) Method ==============================================================='
      SET STATISTICS TIME ON;
  GO
  DECLARE  @RunDay  CHAR(2);
   SELECT @RunDay = CONVERT(CHAR(2), SomeDate, 1)
     FROM #TestTable;
  GO 3
      SET STATISTICS TIME OFF;

  And, yes, I agree.... they all run in almost the same time.  I know we're making million row runs here so a 10 ms difference doesn't matter much but I am seeing the MATH OVERRUN method the Luis posted consistently coming in at about 10ms faster than the rest (about 70ms faster than the CASE method) Are you seeing roughly the same thing?

  
========== PRECAST Method ===============================================================
  Beginning execution loop
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 343 ms,  elapsed time = 333 ms.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 327 ms,  elapsed time = 331 ms.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 328 ms,  elapsed time = 329 ms.
  Batch execution completed 3 times.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.
  ========== DATENAME Method ===============================================================
  Beginning execution loop
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 343 ms,  elapsed time = 336 ms.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 328 ms,  elapsed time = 333 ms.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 343 ms,  elapsed time = 334 ms.
  Batch execution completed 3 times.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.
  ========== MATH OVERRUN Method ===============================================================
  Beginning execution loop
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 312 ms,  elapsed time = 321 ms.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 328 ms,  elapsed time = 320 ms.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 327 ms,  elapsed time = 321 ms.
  Batch execution completed 3 times.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.
  ========== CASE Method ===============================================================
  Beginning execution loop
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 390 ms,  elapsed time = 389 ms.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 375 ms,  elapsed time = 388 ms.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 390 ms,  elapsed time = 390 ms.
  Batch execution completed 3 times.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.
  ========== CONVERT CHAR(2) Method ===============================================================
  Beginning execution loop
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 343 ms,  elapsed time = 344 ms.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 343 ms,  elapsed time = 344 ms.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

   SQL Server Execution Times:
     CPU time = 374 ms,  elapsed time = 374 ms.
  Batch execution completed 3 times.
  SQL Server parse and compile time:
     CPU time = 0 ms, elapsed time = 0 ms.

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

• Phil Parkin

  SSC Guru

  Points: 246977

  More actions

  October 13, 2017 at 5:58 am

  #1963536

  Jeff Moden - Thursday, October 12, 2017 11:41 PM

  Are you seeing roughly the same thing?

  The results I see follow a slightly different pattern. The CASE version is the slowest (and most variable), but the MATH OVERRUN method performs little differently from the remainder.

  
========== PRECAST Method ===============================================================
Beginning execution loop
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 1 ms.

  SQL Server Execution Times:
 CPU time = 360 ms, elapsed time = 357 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 359 ms, elapsed time = 361 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 359 ms, elapsed time = 360 ms.
Batch execution completed 3 times.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.
========== DATENAME Method ===============================================================
Beginning execution loop
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 360 ms, elapsed time = 356 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 359 ms, elapsed time = 358 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 359 ms, elapsed time = 357 ms.
Batch execution completed 3 times.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.
========== MATH OVERRUN Method ===============================================================
Beginning execution loop
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 1 ms.

  SQL Server Execution Times:
 CPU time = 360 ms, elapsed time = 359 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 359 ms, elapsed time = 372 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 360 ms, elapsed time = 363 ms.
Batch execution completed 3 times.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.
========== CASE Method ===============================================================
Beginning execution loop
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 1 ms.

  SQL Server Execution Times:
 CPU time = 468 ms, elapsed time = 482 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 438 ms, elapsed time = 442 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 453 ms, elapsed time = 448 ms.
Batch execution completed 3 times.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.
========== CONVERT CHAR(2) Method ===============================================================
Beginning execution loop
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 343 ms, elapsed time = 349 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 360 ms, elapsed time = 363 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

  SQL Server Execution Times:
 CPU time = 343 ms, elapsed time = 352 ms.
Batch execution completed 3 times.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.


  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Jeff Moden

  SSC Guru

  Points: 1004402

  More actions

  October 13, 2017 at 9:43 am

  #1963597

  Thanks, Phil.  Good to see mostly consistency between disparate machines on these.

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

• Avi1

  SSCrazy

  Points: 2323

  More actions

  October 13, 2017 at 12:40 pm

  #1963631

  I tested the scripts in one of dev box in SQL2016. the results are below. Each scripts ran 100 times (at 2 different times)
  Included the method suggested by Scott Pletcher 

  CONVERT(CHAR(2), SomeDate, 1)

  Run#1
  Method        AvgTime(ms)
  PRECAST Method        553.07    
  DATENAME Method        518.48    
  MATH OVERRUN Method    526.75    
  ScottPletcher Method    483.83    

  Ru#2
  PRECAST Method        565.5    
  DATENAME Method        515.25    
  MATH OVERRUN Method    526.97    
  ScottPletcher Method    501.11