Julian Date

• frederico_fonseca

  SSCoach

  Points: 16030

  More actions

  November 10, 2018 at 12:31 pm

  #2013093

  irehman - Saturday, November 10, 2018 12:19 PM

  frederico_fonseca - Saturday, November 10, 2018 9:31 AM

  one other option (and there are more)

  declare @tbl table
  ( juldate decimal(18, 0)
  , timei float
  )
insert into @tbl select 118063, 11456
insert into @tbl select 099063, 00006

  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
  , convert(time, stuff(stuff(right('000000' + convert(varchar(6), timei), 6), 5, 0, ':'), 3, 0, ':'))
from @tbl

  edit: forgot to add convert time bit - and fixed day add

  @ frederico_fonseca

  I ran
  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
   , convert(time, stuff(stuff(right('000000' + convert(varchar(6), timei), 6), 5, 0, ':'), 3, 0, ':'))
  from @tbl

  I  gives me following error:
  Msg 241, Level 16, State 1, Line 2
  Conversion failed when converting date and/or time from character string.

  was that my code or did you change input data?
  I've just copied what you just posted and it worked fine for me - sql 2014 but should also work on 2016

• frederico_fonseca

  SSCoach

  Points: 16030

  More actions

  November 10, 2018 at 12:34 pm

  #2013094

  Jeff Moden - Saturday, November 10, 2018 11:30 AM

  Thanks, Frederico.  Time for some play.

  just in case having a single value was an issue I changed my insert to
  

  create table #tbl
( juldate1 decimal(18, 0)
, juldate decimal(18, 0) -- so I didn't have to change remaining code
)
;

  while @rows1 < 1000000
begin
insert into #tbl select 118063 , 118*1000 + (@rows1 % 999) * 1000 + @Rows1 % 364;
set @rows1 = @rows1 + 1
end
  giving a total of 363636 distinct values to process.

  results are the same

• frederico_fonseca

  SSCoach

  Points: 16030

  More actions

  November 10, 2018 at 12:37 pm

  #2013095

  frederico_fonseca - Saturday, November 10, 2018 12:31 PM

  irehman - Saturday, November 10, 2018 12:19 PM

  frederico_fonseca - Saturday, November 10, 2018 9:31 AM

  one other option (and there are more)

  declare @tbl table
  ( juldate decimal(18, 0)
  , timei float
  )
insert into @tbl select 118063, 11456
insert into @tbl select 099063, 00006

  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
  , convert(time, stuff(stuff(right('000000' + convert(varchar(6), timei), 6), 5, 0, ':'), 3, 0, ':'))
from @tbl

  edit: forgot to add convert time bit - and fixed day add

  @ frederico_fonseca

  I ran
  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
   , convert(time, stuff(stuff(right('000000' + convert(varchar(6), timei), 6), 5, 0, ':'), 3, 0, ':'))
  from @tbl

  I  gives me following error:
  Msg 241, Level 16, State 1, Line 2
  Conversion failed when converting date and/or time from character string.

  was that my code or did you change input data?
  I've just copied what you just posted and it worked fine for me - sql 2014 but should also work on 2016

  your float may have decimal values.
  try the following - converting the time to int before converting to varchar

  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
, convert(time, stuff(stuff(right('000000' + convert(varchar(6), convert(int, timei)), 6), 5, 0, ':'), 3, 0, ':'))
from @tbl

• irehman

  SSC-Addicted

  Points: 496

  More actions

  November 10, 2018 at 12:46 pm

  #2013096

  frederico_fonseca - Saturday, November 10, 2018 12:37 PM

  frederico_fonseca - Saturday, November 10, 2018 12:31 PM

  irehman - Saturday, November 10, 2018 12:19 PM

  frederico_fonseca - Saturday, November 10, 2018 9:31 AM

  one other option (and there are more)

  declare @tbl table
  ( juldate decimal(18, 0)
  , timei float
  )
insert into @tbl select 118063, 11456
insert into @tbl select 099063, 00006

  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
  , convert(time, stuff(stuff(right('000000' + convert(varchar(6), timei), 6), 5, 0, ':'), 3, 0, ':'))
from @tbl

  edit: forgot to add convert time bit - and fixed day add

  @ frederico_fonseca

  I ran
  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
   , convert(time, stuff(stuff(right('000000' + convert(varchar(6), timei), 6), 5, 0, ':'), 3, 0, ':'))
  from @tbl

  I  gives me following error:
  Msg 241, Level 16, State 1, Line 2
  Conversion failed when converting date and/or time from character string.

  was that my code or did you change input data?
  I've just copied what you just posted and it worked fine for me - sql 2014 but should also work on 2016

  your float may have decimal values.
  try the following - converting the time to int before converting to varchar

  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
, convert(time, stuff(stuff(right('000000' + convert(varchar(6), convert(int, timei)), 6), 5, 0, ':'), 3, 0, ':'))
from @tbl

  Still the same issue

• irehman

  SSC-Addicted

  Points: 496

  More actions

  November 10, 2018 at 12:49 pm

  #2013097

  Jeff Moden - Saturday, November 10, 2018 9:23 AM

  This should do it for you
  
--===== Create a sample using variables.
DECLARE  @OrderDate NUMERIC(18,0)   = 118063
        ,@OrderTime FLOAT           = 11456
;
--===== This solves for the two separate columns and a combination of the two columns.
     -- Replace the @OrderDate and @OrderTime variables with column names if you want to play this against a table.
 SELECT  OrderDate      = CONVERT(DATE,DATEADD(dy,@OrderDate%1000-1,DATEADD(yy,@OrderDate/1000,0)))
        ,OrderTime      = CONVERT(TIME(0),DATEADD(hh,CONVERT(INT,@OrderTime)/10000%100,DATEADD(mi,CONVERT(INT,@OrderTime)/100%100,DATEADD(ss,CONVERT(INT,@OrderTime)%100,0))))
        ,OrderDateTime  = DATEADD(dy,@OrderDate%1000-1,DATEADD(yy,@OrderDate/1000,0))
                        + DATEADD(hh,CONVERT(INT,@OrderTime)/10000%100,DATEADD(mi,CONVERT(INT,@OrderTime)/100%100,DATEADD(ss,CONVERT(INT,@OrderTime)%100,0)))
;


  so Jeff, I just replaced
  @OrderDate NUMERIC(18,0) with @OrderDate NUMERIC(18,0) FROM Order?
  @OrderTime FLOAT   with @OrderTime FLOAT  FROM Order?
  Sorry, I'm new to SQL.

• frederico_fonseca

  SSCoach

  Points: 16030

  More actions

  November 10, 2018 at 12:51 pm

  #2013098

  irehman - Saturday, November 10, 2018 12:46 PM

  frederico_fonseca - Saturday, November 10, 2018 12:37 PM

  frederico_fonseca - Saturday, November 10, 2018 12:31 PM

  irehman - Saturday, November 10, 2018 12:19 PM

  frederico_fonseca - Saturday, November 10, 2018 9:31 AM

  one other option (and there are more)

  declare @tbl table
  ( juldate decimal(18, 0)
  , timei float
  )
insert into @tbl select 118063, 11456
insert into @tbl select 099063, 00006

  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
  , convert(time, stuff(stuff(right('000000' + convert(varchar(6), timei), 6), 5, 0, ':'), 3, 0, ':'))
from @tbl

  edit: forgot to add convert time bit - and fixed day add

  @ frederico_fonseca

  I ran
  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
   , convert(time, stuff(stuff(right('000000' + convert(varchar(6), timei), 6), 5, 0, ':'), 3, 0, ':'))
  from @tbl

  I  gives me following error:
  Msg 241, Level 16, State 1, Line 2
  Conversion failed when converting date and/or time from character string.

  was that my code or did you change input data?
  I've just copied what you just posted and it worked fine for me - sql 2014 but should also work on 2016

  your float may have decimal values.
  try the following - converting the time to int before converting to varchar

  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
, convert(time, stuff(stuff(right('000000' + convert(varchar(6), convert(int, timei)), 6), 5, 0, ':'), 3, 0, ':'))
from @tbl

  Still the same issue

  you telling us that if you copy and paste the code below it gives you the error? or as I asked on the previous post you are changing the input data?
  
declare @tbl table
( juldate decimal(18, 0)
, timei float
)
insert into @tbl select 118063, 11456
insert into @tbl select 099063, 00006.1

  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
 , convert(time, stuff(stuff(right('000000' + convert(varchar(6), convert(int, timei)), 6), 5, 0, ':'), 3, 0, ':'))
from @tbl

  if it is copy and paste than I do not know what could be the issue
  running the above gives me this output
  2018-03-04    01:14:56.0000000
  1999-03-04    00:00:06.0000000

• irehman

  SSC-Addicted

  Points: 496

  More actions

  November 10, 2018 at 1:01 pm

  #2013099

  frederico_fonseca - Saturday, November 10, 2018 12:31 PM

  irehman - Saturday, November 10, 2018 12:19 PM

  frederico_fonseca - Saturday, November 10, 2018 9:31 AM

  one other option (and there are more)

  declare @tbl table
  ( juldate decimal(18, 0)
  , timei float
  )
insert into @tbl select 118063, 11456
insert into @tbl select 099063, 00006

  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
  , convert(time, stuff(stuff(right('000000' + convert(varchar(6), timei), 6), 5, 0, ':'), 3, 0, ':'))
from @tbl

  edit: forgot to add convert time bit - and fixed day add

  @ frederico_fonseca

  I ran
  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112))
   , convert(time, stuff(stuff(right('000000' + convert(varchar(6), timei), 6), 5, 0, ':'), 3, 0, ':'))
  from @tbl

  I  gives me following error:
  Msg 241, Level 16, State 1, Line 2
  Conversion failed when converting date and/or time from character string.

  was that my code or did you change input data?
  I've just copied what you just posted and it worked fine for me - sql 2014 but should also work on 2016

  I'm using SQL 2012

• irehman

  SSC-Addicted

  Points: 496

  More actions

  November 10, 2018 at 1:14 pm

  #2013100

  Jeff Moden - Saturday, November 10, 2018 9:23 AM

  This should do it for you
  
--===== Create a sample using variables.
DECLARE  @OrderDate NUMERIC(18,0)   = 118063
        ,@OrderTime FLOAT           = 11456
;
--===== This solves for the two separate columns and a combination of the two columns.
     -- Replace the @OrderDate and @OrderTime variables with column names if you want to play this against a table.
 SELECT  OrderDate      = CONVERT(DATE,DATEADD(dy,@OrderDate%1000-1,DATEADD(yy,@OrderDate/1000,0)))
        ,OrderTime      = CONVERT(TIME(0),DATEADD(hh,CONVERT(INT,@OrderTime)/10000%100,DATEADD(mi,CONVERT(INT,@OrderTime)/100%100,DATEADD(ss,CONVERT(INT,@OrderTime)%100,0))))
        ,OrderDateTime  = DATEADD(dy,@OrderDate%1000-1,DATEADD(yy,@OrderDate/1000,0))
                        + DATEADD(hh,CONVERT(INT,@OrderTime)/10000%100,DATEADD(mi,CONVERT(INT,@OrderTime)/100%100,DATEADD(ss,CONVERT(INT,@OrderTime)%100,0)))
;


  so Jeff, I just replaced
  @OrderDate NUMERIC(18,0) with @OrderDate NUMERIC(18,0) FROM Order?
  @OrderTime FLOAT with @OrderTime FLOAT FROM Order?
  Sorry, I'm new to SQL.

• Jeff Moden

  SSC Guru

  Points: 1003875

  More actions

  November 10, 2018 at 1:24 pm

  #2013101

  irehman - Saturday, November 10, 2018 12:49 PM

  Jeff Moden - Saturday, November 10, 2018 9:23 AM

  This should do it for you
  
--===== Create a sample using variables.
DECLARE  @OrderDate NUMERIC(18,0)   = 118063
        ,@OrderTime FLOAT           = 11456
;
--===== This solves for the two separate columns and a combination of the two columns.
     -- Replace the @OrderDate and @OrderTime variables with column names if you want to play this against a table.
 SELECT  OrderDate      = CONVERT(DATE,DATEADD(dy,@OrderDate%1000-1,DATEADD(yy,@OrderDate/1000,0)))
        ,OrderTime      = CONVERT(TIME(0),DATEADD(hh,CONVERT(INT,@OrderTime)/10000%100,DATEADD(mi,CONVERT(INT,@OrderTime)/100%100,DATEADD(ss,CONVERT(INT,@OrderTime)%100,0))))
        ,OrderDateTime  = DATEADD(dy,@OrderDate%1000-1,DATEADD(yy,@OrderDate/1000,0))
                        + DATEADD(hh,CONVERT(INT,@OrderTime)/10000%100,DATEADD(mi,CONVERT(INT,@OrderTime)/100%100,DATEADD(ss,CONVERT(INT,@OrderTime)%100,0)))
;


  so Jeff, I just replaced
  @OrderDate NUMERIC(18,0) with @OrderDate NUMERIC(18,0) FROM Order?
  @OrderTime FLOAT   with @OrderTime FLOAT  FROM Order?
  Sorry, I'm new to SQL.

  No.  You have to use the formulas as given.  Like this...

  
 SELECT  OrderDate = CONVERT(DATE
                         ,DATEADD(dy,OrderDate%1000-1    --Parse and convert the days of the year to add to the...
                         ,DATEADD(yy,OrderDate/1000,0))) --Parsed and converted year. "0" is date of 1900-01-01.
        ,OrderTime = CONVERT(TIME(0)
                         ,DATEADD(hh,CONVERT(INT,OrderTime)/10000%100 --Parse and convert the hour portion
                         ,DATEADD(mi,CONVERT(INT,OrderTime)/100%100   --Parse and convert the minutes portion
                         ,DATEADD(ss,CONVERT(INT,OrderTime)%100,0)))) --Parse and convert the seconds portion
   FROM dbo.Order
;


  Notice that no variables are involved this time... just column names from the table and new column names (they're called "column Aliases" when used to create "derived" columns like this) to the left of the "=" sign (or after an "AS" if you like things the other way better)

  You should also always use the 2 part (SchemaName.TableName) naming convention for objects (tables, functions, stored procedures, views, etc) in SQL Server.

  In the above, I also "wrapped" the formulas around at "logical breaks" to make it easier to read instead of having to scroll to the right to read the whole code.  It also makes it easier to comment the code so that new people can figure out what's going on. 😀

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

• irehman

  SSC-Addicted

  Points: 496

  More actions

  November 10, 2018 at 1:32 pm

  #2013102

  Jeff Moden - Saturday, November 10, 2018 1:24 PM

  irehman - Saturday, November 10, 2018 12:49 PM

  Jeff Moden - Saturday, November 10, 2018 9:23 AM

  This should do it for you
  
--===== Create a sample using variables.
DECLARE  @OrderDate NUMERIC(18,0)   = 118063
        ,@OrderTime FLOAT           = 11456
;
--===== This solves for the two separate columns and a combination of the two columns.
     -- Replace the @OrderDate and @OrderTime variables with column names if you want to play this against a table.
 SELECT  OrderDate      = CONVERT(DATE,DATEADD(dy,@OrderDate%1000-1,DATEADD(yy,@OrderDate/1000,0)))
        ,OrderTime      = CONVERT(TIME(0),DATEADD(hh,CONVERT(INT,@OrderTime)/10000%100,DATEADD(mi,CONVERT(INT,@OrderTime)/100%100,DATEADD(ss,CONVERT(INT,@OrderTime)%100,0))))
        ,OrderDateTime  = DATEADD(dy,@OrderDate%1000-1,DATEADD(yy,@OrderDate/1000,0))
                        + DATEADD(hh,CONVERT(INT,@OrderTime)/10000%100,DATEADD(mi,CONVERT(INT,@OrderTime)/100%100,DATEADD(ss,CONVERT(INT,@OrderTime)%100,0)))
;


  so Jeff, I just replaced
  @OrderDate NUMERIC(18,0) with @OrderDate NUMERIC(18,0) FROM Order?
  @OrderTime FLOAT   with @OrderTime FLOAT  FROM Order?
  Sorry, I'm new to SQL.

  No.  You have to use the formulas as given.  Like this...

  
 SELECT  OrderDate = CONVERT(DATE
                         ,DATEADD(dy,OrderDate%1000-1    --Parse and convert the days of the year to add to the...
                         ,DATEADD(yy,OrderDate/1000,0))) --Parsed and converted year. "0" is date of 1900-01-01.
        ,OrderTime = CONVERT(TIME(0)
                         ,DATEADD(hh,CONVERT(INT,OrderTime)/10000%100 --Parse and convert the hour portion
                         ,DATEADD(mi,CONVERT(INT,OrderTime)/100%100   --Parse and convert the minutes portion
                         ,DATEADD(ss,CONVERT(INT,OrderTime)%100,0)))) --Parse and convert the seconds portion
   FROM dbo.Order
;


  Notice that no variables are involved this time... just column names from the table and new column names (they're called "column Aliases" when used to create "derived" columns like this) to the left of the "=" sign (or after an "AS" if you like things the other way better)

  You should also always use the 2 part (SchemaName.TableName) naming convention for objects (tables, functions, stored procedures, views, etc) in SQL Server.

  In the above, I also "wrapped" the formulas around at "logical breaks" to make it easier to read instead of having to scroll to the right to read the whole code.  It also makes it easier to comment the code so that new people can figure out what's going on. 😀

  WORKED .... WORKED .... WORKED.... Thanks

• Jeff Moden

  SSC Guru

  Points: 1003875

  More actions

  November 10, 2018 at 1:36 pm

  #2013103

  Ok... so nothing has changed much in the last couple of decades for this type of conversion.  A trip through character based code to do such conversions is still a fair bit slower (3X longer) and, apparently, I was right about the cardinality of 1 thing.  The differences are much more significant when you have a higher cardinality.

  Here's the test harness I created for this test (1 Million rows).  Details are in the comments... some goodies there.  It's all done in TempDB and you can just run the whole shootin' match once it's passes your visual safety check.  Don't let the million row thing scare anyone... it only takes a couple of seconds to execute.
  
RAISERROR('
--=====================================================================================================================
--      PRESETS
--=====================================================================================================================
',0,0) WITH NOWAIT
;
--===== If the test table already exists, drop it to make reruns in SSMS easier.
     IF OBJECT_ID('tempdb..#DateTest','u') IS NOT NULL
   DROP TABLE #DateTest
;
GO
RAISERROR('
--=====================================================================================================================
--      Create the table and populate it with random data on the fly.
--=====================================================================================================================
',0,0) WITH NOWAIT
;
--===== Create a million row test table according to the OP's original column specifications.
     -- The generated dates and times in RandDT column are included for sanity checks.
     -- The dates and times vary from 01 Jan 1900 up to and not including 01 Jan 2100.
     -- This code takes about 2.5 seconds on my i5 powered laptop using SQL Server 2008 Developers Edition.
     -- For more information on how to rapidly generate random test data, please see the following articles.
     -- http://www.sqlservercentral.com/articles/Data+Generation/87901/
     -- http://www.sqlservercentral.com/articles/Test+Data/88964/
    SET STATISTICS TIME,IO ON
;
   WITH cteDT AS
(
 SELECT TOP 1000000
        RandDT = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'1900','2100')+CONVERT(DATETIME,0)
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
)
 SELECT  RandDT
        ,OrderDate = CONVERT(NUMERIC(18,0),DATEDIFF(yy,0,RandDT)*1000 + DATEPART(dy,RandDT))
        ,OrderTime = CONVERT(FLOAT,DATEPART(hh,RandDT)*10000+DATEPART(mi,RandDT)*100+DATEPART(ss,RandDT))
   INTO #DateTest
   FROM cteDT
;
    SET STATISTICS TIME,IO OFF
;
GO
/*
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 1. In the following tests, we'll declare some "dump" variables for use during each test.
    We don't want saving to disk or displaying to the screen to obfuscate the performance differences in the code so
    we'll dump all results in variables, which is memory and that's very fast.
 2. In each test...
    2.1 Proccache is cleared.
    2.2 Clean buffers are dropped.
    2.3 Each test is executed 5 times.
        2.3.1 The first execution inherently includes "Physical Read and Read Ahead" time.
        2.3.2 The other four executions inherently doesn't include "Physical Read or Read Ahead" because the data is
              already in memory.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
*/
GO
RAISERROR('
--=====================================================================================================================
--      Test #1: Integer Math
--=====================================================================================================================
',0,0) WITH NOWAIT
;
--===== Clear cache and drop clean buffers. The time to do this is NOT included in the output.
    DBCC FREEPROCCACHE;
    DBCC DROPCLEANBUFFERS
;
--===== Print a run separation line for readability
  PRINT REPLICATE('-',119)
;
GO
--===== Create the "Dump" variables. The time to do this is not included in the output.
DECLARE  @OrderDate DATE
        ,@OrderTime TIME
;
--===== Start the timers
    SET STATISTICS TIME,IO ON
;
--===== Run the test
 SELECT  @OrderDate = CONVERT(DATE
                         ,DATEADD(dy,OrderDate%1000-1    --Parse and convert the days of the year to add to the...
                         ,DATEADD(yy,OrderDate/1000,0))) --Parsed and converrted year. "0" is date of 1900-01-01.
        ,@OrderTime = CONVERT(TIME(0)
                         ,DATEADD(hh,CONVERT(INT,OrderTime)/10000%100 --Parse and convert the hour portion
                         ,DATEADD(mi,CONVERT(INT,OrderTime)/100%100   --Parse and convert the minutes portion
                         ,DATEADD(ss,CONVERT(INT,OrderTime)%100,0)))) --Parse and convert the seconds portion
   FROM #DateTest
;
--===== Stop the timers
    SET STATISTICS TIME,IO OFF
;
--===== Print a run separation line for readability
  PRINT REPLICATE('-',119)
;
--===== Execute the test a total of 5 times
GO 5

  RAISERROR('
  --=====================================================================================================================
  --      Test #2: Character Conversion
  --=====================================================================================================================
  ',0,0) WITH NOWAIT
  ;
  --===== Clear cache and drop clean buffers. The time to do this is NOT included in the output.
      DBCC FREEPROCCACHE;
      DBCC DROPCLEANBUFFERS
  ;
  --===== Print a run separation line for readability
    PRINT REPLICATE('-',119)
  ;
  GO
  --===== Create the "Dump" variables. The time to do this is included in the output.
  DECLARE  @OrderDate DATE
          ,@OrderTime TIME
  ;
  --===== Start the timers
      SET STATISTICS TIME,IO ON
  ;
  --===== Run the test
   SELECT  @OrderDate = dateadd(day, OrderDate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, OrderDate) / 1000, 0)) + '1231', 112))
          ,@OrderTime = convert(time, stuff(stuff(right('000000' + convert(varchar(6), OrderTime), 6), 5, 0, ':'), 3, 0, ':'))
     FROM #DateTest
  ;
  --===== Stop the timers
      SET STATISTICS TIME,IO OFF
  ;
  --===== Print a run separation line for readability
    PRINT REPLICATE('-',119)
  ;
  --===== Execute the test a total of 5 times
  GO 5
  RAISERROR('
  --=====================================================================================================================
  --      Test Complete
  --=====================================================================================================================
  ',0,0) WITH NOWAIT
  ;

  Here are the results from my laptop...
  

  --=====================================================================================================================
--      PRESETS
--=====================================================================================================================

  --=====================================================================================================================
  --      Create the table and populate it with random data on the fly.
  --=====================================================================================================================
  Table 'syscolrdb'. Scan count 1, logical reads 110, physical reads 7, read-ahead reads 116, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'syscolpars'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 17, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 936 ms,  elapsed time = 2211 ms.

  (1000000 row(s) affected)

  --=====================================================================================================================
  --      Test #1: Integer Math
  --=====================================================================================================================
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  -----------------------------------------------------------------------------------------------------------------------
  Beginning execution loop
  Table '#DateTest___________________________________________________________________________________________________________0000000000FB'.
  Scan count 1, logical reads 4203, physical reads 1, read-ahead reads 4208, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 733 ms,  elapsed time = 934 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000FB'.
  Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 765 ms,  elapsed time = 760 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000FB'.
  Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 733 ms,  elapsed time = 742 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000FB'.
  Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 733 ms,  elapsed time = 734 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000FB'.
  Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 733 ms,  elapsed time = 725 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Batch execution completed 5 times.

  --=====================================================================================================================
  --      Test #2: Character Conversion
  --=====================================================================================================================
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  -----------------------------------------------------------------------------------------------------------------------
  Beginning execution loop
  Table '#DateTest___________________________________________________________________________________________________________0000000000FB'.
  Scan count 1, logical reads 4203, physical reads 1, read-ahead reads 4208, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 2278 ms,  elapsed time = 2307 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000FB'.
  Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 2231 ms,  elapsed time = 2225 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000FB'.
  Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 2215 ms,  elapsed time = 2216 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000FB'.
  Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 2231 ms,  elapsed time = 2225 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000FB'.
  Scan count 1, logical reads 4203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

   SQL Server Execution Times:
     CPU time = 2231 ms,  elapsed time = 2236 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Batch execution completed 5 times.

  --=====================================================================================================================
  --      Test Complete
  --=====================================================================================================================
  

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

• frederico_fonseca

  SSCoach

  Points: 16030

  More actions

  November 10, 2018 at 2:06 pm

  #2013105

  similar results for me 

  As the results I had given before only related to the date part I've also run your code with just the date bit.
  and also without the statistics time as they can mess up function call

  with statistics on
  --  Test #1: Integer Math
  CPU time = 453 ms, elapsed time = 457 ms.
  --  Test #2: Character Conversion
   CPU time = 687 ms, elapsed time = 684 ms.
  with getdate
  --  Test #1: Integer Math
  duration 1 = 463
  --  Test #2: Character Conversion
  duration 1 = 686

  below is run with your exact code

  --=====================================================================================================================
  --  PRESETS
  --=====================================================================================================================

  --=====================================================================================================================
  --  Create the table and populate it with random data on the fly.
  --=====================================================================================================================
  Table 'syscolpars'. Scan count 1, logical reads 413, physical reads 0, read-ahead reads 431, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'syscolpars'. Scan count 2, logical reads 11, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 795 ms, elapsed time = 886 ms.

  (1000000 row(s) affected)

  --=====================================================================================================================
  --  Test #1: Integer Math
  --=====================================================================================================================
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  -----------------------------------------------------------------------------------------------------------------------
  Beginning execution loop
  Table '#DateTest___________________________________________________________________________________________________________0000000000C7'. Scan count 1, logical reads 4202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 795 ms, elapsed time = 793 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000C7'. Scan count 1, logical reads 4202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 780 ms, elapsed time = 795 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000C7'. Scan count 1, logical reads 4202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 812 ms, elapsed time = 805 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000C7'. Scan count 1, logical reads 4202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 795 ms, elapsed time = 795 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000C7'. Scan count 1, logical reads 4202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 811 ms, elapsed time = 808 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Batch execution completed 5 times.

  --=====================================================================================================================
  --  Test #2: Character Conversion
  --=====================================================================================================================
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  -----------------------------------------------------------------------------------------------------------------------
  Beginning execution loop
  Table '#DateTest___________________________________________________________________________________________________________0000000000C7'. Scan count 1, logical reads 4202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 2106 ms, elapsed time = 2127 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000C7'. Scan count 1, logical reads 4202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 2137 ms, elapsed time = 2135 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000C7'. Scan count 1, logical reads 4202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 2137 ms, elapsed time = 2136 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000C7'. Scan count 1, logical reads 4202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 2184 ms, elapsed time = 2182 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Table '#DateTest___________________________________________________________________________________________________________0000000000C7'. Scan count 1, logical reads 4202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
   CPU time = 2138 ms, elapsed time = 2149 ms.
  -----------------------------------------------------------------------------------------------------------------------
  Batch execution completed 5 times.

  --=====================================================================================================================
  --  Test Complete
  --=====================================================================================================================

• jcelko212 32090

  SSCrazy Eights

  Points: 9295

  More actions

  November 10, 2018 at 2:54 pm

  #2013108

  irehman - Saturday, November 10, 2018 8:25 AM

  Jeff has given you a kludge it will get you started, but you really need to throw this mess out and redo it. I'm sorry you have only one "Order", as shown by your singular table name. I'm also sorry that you choose to use a reserved word from SQL is a table name.

  The Julian date is a rather large number that's defined by astronomers. You might have meant an ordinal date which is defined by ISO 8601. But but looking at that insane data, this is something you invented yourself and has no standards.

  Since time is continuous, splitting the date and the time of an event into two separate columns is a classic design error called attribute splitting. They are part of one measurement should never be split apart. This is what the ISO and ANSI standards calls a TIMESTAMP, and it's not broken into two parts like this.

  Do you actually need the time component? Generally orders are done on a daily basis, not down to the seconds. The original SQL Server from Sybase was designed for UNIX systems on 16-bit minicomputers decades ago. It had a datetime only, which was represented as a floating-point number internally. This is one of the reasons that it has all kinds of funny properties. We have the ANSI ISO standard DATE data type today.

  You also need to download the free PDF book from the University of Arizona website on temporal queries in SQL by Rick Snodgrass.

  Please post DDL and follow ANSI/ISO standards when asking for help. 

• irehman

  SSC-Addicted

  Points: 496

  More actions

  November 10, 2018 at 3:32 pm

  #2013109

  jcelko212 32090 - Saturday, November 10, 2018 2:54 PM

  irehman - Saturday, November 10, 2018 8:25 AM

  Jeff has given you a kludge it will get you started, but you really need to throw this mess out and redo it. I'm sorry you have only one "Order", as shown by your singular table name. I'm also sorry that you choose to use a reserved word from SQL is a table name.

  The Julian date is a rather large number that's defined by astronomers. You might have meant an ordinal date which is defined by ISO 8601. But but looking at that insane data, this is something you invented yourself and has no standards.

  Since time is continuous, splitting the date and the time of an event into two separate columns is a classic design error called attribute splitting. They are part of one measurement should never be split apart. This is what the ISO and ANSI standards calls a TIMESTAMP, and it's not broken into two parts like this.

  Do you actually need the time component? Generally orders are done on a daily basis, not down to the seconds. The original SQL Server from Sybase was designed for UNIX systems on 16-bit minicomputers decades ago. It had a datetime only, which was represented as a floating-point number internally. This is one of the reasons that it has all kinds of funny properties. We have the ANSI ISO standard DATE data type today.

  You also need to download the free PDF book from the University of Arizona website on temporal queries in SQL by Rick Snodgrass.

  Thanks, what Jeff gave me is working great.

• Jeff Moden

  SSC Guru

  Points: 1003875

  More actions

  November 10, 2018 at 9:37 pm

  #2013113

  jcelko212 32090 - Saturday, November 10, 2018 2:54 PM

  irehman - Saturday, November 10, 2018 8:25 AM

  Jeff has given you a kludge it will get you started, but you really need to throw this mess out and redo it. I'm sorry you have only one "Order", as shown by your singular table name. I'm also sorry that you choose to use a reserved word from SQL is a table name.

  The Julian date is a rather large number that's defined by astronomers. You might have meant an ordinal date which is defined by ISO 8601. But but looking at that insane data, this is something you invented yourself and has no standards.

  Since time is continuous, splitting the date and the time of an event into two separate columns is a classic design error called attribute splitting. They are part of one measurement should never be split apart. This is what the ISO and ANSI standards calls a TIMESTAMP, and it's not broken into two parts like this.

  Do you actually need the time component? Generally orders are done on a daily basis, not down to the seconds. The original SQL Server from Sybase was designed for UNIX systems on 16-bit minicomputers decades ago. It had a datetime only, which was represented as a floating-point number internally. This is one of the reasons that it has all kinds of funny properties. We have the ANSI ISO standard DATE data type today.

  You also need to download the free PDF book from the University of Arizona website on temporal queries in SQL by Rick Snodgrass.

  While I agree that splitting date and time into separate columns is a mistake, it's something that people have to put up with every day.  And, no... this isn't astronomy and that's why I asked for the data example instead of relying on many incorrect or esoteric definitions of what a Julian date is.

  I also gave the OP a solution that works for the unfortunate data that the OP was provided.  What is your coded solution for such a case, Joe?  As usual, though, I don't actually expect one from you.

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