Julian Date

• irehman

  SSC-Addicted

  Points: 496

  More actions

  November 10, 2018 at 8:26 am

  #376366

  I have a table called ORDER with columns OrderDate and OrderTime both are in Julian Date format. I'm trying to create a view on top of Order Table, but I need to convert OradeDate and OrderTime columns to Calendar Date and Calendar Time.

  How do I do that?

  Thanks in advance.

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 10, 2018 at 8:32 am

  #2013071

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

  I have a table called ORDER with columns OrderDate and OrderTime both are in Julian Date format. I'm trying to create a view on top of Order Table, but I need to convert OradeDate and OrderTime columns to Calendar Date and Calendar Time.

  How do I do that?

  Thanks in advance.

  Considering the many improper definitions of what a "Julian Date" format is, what does the data in the OrderDate and OrderTime columns actually look like and what is the datatype of those two columns?

  --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 8:41 am

  #2013072

  @jeff Moden

  OrderDate: Sample Data: 118063  and data type  is Numeric (18,0)
  OrderTime: Sample Data: 11456  and data type is Float

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 10, 2018 at 8:49 am

  #2013073

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

  @jeff Moden

  OrderDate: Sample Data: 118063  and data type  is Numeric (18,0)
  OrderTime: Sample Data: 11456  and data type is Float

  Thanks

  OK.  Just to verify, the date that you posted above is supposed to be returned as the calendar date of the 63rd day of the year 2018 and the time is supposed to be "01:14:56" (hh:mi:ss), correct?

  --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 8:54 am

  #2013074

  Jeff Moden, yes

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 10, 2018 at 9:13 am

  #2013075

  K.  I'm on it.  Back in a few.

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

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 10, 2018 at 9:23 am

  #2013076

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


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

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 10, 2018 at 9:27 am

  #2013077

  p.s.  If you don't understand the code and how it does what it does, post back.  You're the one that will have to support the code.

  --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: 16017

  More actions

  November 10, 2018 at 9:31 am

  #2013078

  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

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 10, 2018 at 9:53 am

  #2013080

  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

  Cool.  The only thing is that I try to avoid trips through the VARCHAR world for such things.  On today's machines, it might not make much of a performance difference until you get over a million rows but I work with tables and staging tables that have hundreds of millions of rows and multiple columns that would require such a conversion.

  I'll also admit that I've not tested such a thing in over a decade so I could be incorrect on avoiding character based conversions for stuff like this.  I should probably do another test to see if the old findings are still true.

  --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: 16017

  More actions

  November 10, 2018 at 10:36 am

  #2013085

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

  Cool.  The only thing is that I try to avoid trips through the VARCHAR world for such things.  On today's machines, it might not make much of a performance difference until you get over a million rows but I work with tables and staging tables that have hundreds of millions of rows and multiple columns that would require such a conversion.

  I'll also admit that I've not tested such a thing in over a decade so I could be incorrect on avoiding character based conversions for stuff like this.  I should probably do another test to see if the old findings are still true.

  it sill has a overhead.
  using mine and your code, over a 100k row table with 2 columns as per above, 

  insert into #tblout with (tablockx)
    select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112)) as orderdate

    from #tbl with (tablockx)

  average over 10 executions each (in miliseconds)
  mine : 90 
  yours: 60
  if I change my code dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + convert(int, juldate) / 1000) + '1231', 112)) as orderdate
  my times go down to 85  - round overhead not that big.

  with 1 million rows  (in miliseconds)
  mine 1: 897
  mine 2: 853
  yours: 589

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 10, 2018 at 10:48 am

  #2013086

  Can we see the code you used to generate the test data?  My concern is that it may have a cardinality of "1" and that can lead to some pretty nasty differences in performance one way or the other.

  --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: 16017

  More actions

  November 10, 2018 at 11:03 am

  #2013087

  Jeff Moden - Saturday, November 10, 2018 10:48 AM

  Can we see the code you used to generate the test data?  My concern is that it may have a cardinality of "1" and that can lead to some pretty nasty differences in performance one way or the other.

  nothing fancy and it may indeed suffer from that - not a proper test 😀

  set nocount on
if object_id('tempdb..#tbl') is not null
drop table #tbl;

  create table #tbl
( juldate decimal(18, 0)
, timei float
)
;

  if object_id('tempdb..#tblout') is not null
drop table #tblout;

  create table #tblout
( orderdate date
)
;
declare @rows1 int = 0

  while @rows1 < 1000000
begin
insert into #tbl select 118063, 11456;
set @rows1 = @rows1 + 1
end

  declare @rows int = 0
declare @start time 
declare @end time 
declare @duration time

  set @Rows = 0
while @rows < 10
begin
  truncate table #tblout
  set @Start = getdate();
  insert into #tblout with (tablockx)
  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + round(convert(int, juldate) / 1000, 0)) + '1231', 112)) as orderdate

    from #tbl with (tablockx)

    set @End = getdate()

    print 'duration 1 = ' + convert(varchar(10), datediff(millisecond, @start, @End))
  set @Rows = @Rows + 1
end

  set @Rows = 0
while @rows < 10
begin
  truncate table #tblout
  set @Start = getdate();
  insert into #tblout with (tablockx)
  select dateadd(day, juldate % 1000, convert(date, convert(varchar(4), 1899 + convert(int, juldate) / 1000) + '1231', 112)) as orderdate

    from #tbl with (tablockx)

    set @End = getdate()

    print 'duration 1b = ' + convert(varchar(10), datediff(millisecond, @start, @End))
  set @Rows = @Rows + 1
end

  set @Rows = 0
while @rows < 10
begin
  truncate table #tblout
  set @Start = getdate();
  insert into #tblout with (tablockx)
  select OrderDate  = CONVERT(DATE,DATEADD(dy,juldate%1000-1,DATEADD(yy,juldate/1000,0)))

    from #tbl with (tablockx)

    set @End = getdate()

    print 'duration 2 = ' + convert(varchar(10), datediff(millisecond, @start, @End))
  set @Rows = @Rows + 1
end


• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  November 10, 2018 at 11:30 am

  #2013088

  Thanks, Frederico.  Time for some play.

  --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 12:19 pm

  #2013092

  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.