Conversion from Julian to Greogrian Date

Question

I am having trouble understanding the code below; Val1 and Val2 are in Julian format.
Is there a better way to accomplish this?

Val1 = 203510;  
Val2 = 122189;  

   SELECT * from MyTable  
	dateadd(hour,cast(substring(right('000000'+cast(Val1 as nvarchar),6),1,2) as dec(2,0)),   
	dateadd(minute,cast(substring(right('000000'+cast(Val1 as nvarchar),6),3,2) as dec(2,0)),   
	dateadd(second,cast(substring(right('000000'+cast(Val1 as nvarchar),6),5,2) as dec(2,0)),   
	cast(convert(varchar,dateadd(dy,cast(substring(cast(Val2+1900000 as nvarchar),5,3) as int)-1,  
	substring(cast(Val2+1900000 as nvarchar),1,4)+'0101'),101) as datetime))))   
	between 'Jul 10 2022  2:30PM' and 'Jul 13 2022 10:44PM'   
  

Answer 1

It looks like the first 2 digits of val1 is the hour, the next 2 digits is the minute, and the last 2 digits is the seconds. In val2 the first 3 digits are the number of years since 1900 and the last 3 digits are the date number (so Jan 1 is 1, Jan 2 is 2, etc until Dec 31 is 365 (or 366 if it's a leap year). So you could do

Declare @Test Table(Val1 int, val2 int);  
Insert @Test(val1, val2) Values(203510, 122189);  
;With cte As  
(Select val1 % 100 As Seconds,  
  (val1/100) % 100 As Minutes,  
  val1/10000 As Hours,  
  DateAdd(day, val2%1000, Cast((1900+(val2/1000) - 1) As char(4)) + '1231') As YMD   
From @Test)  
Select DateTimeFromParts(Year(YMD), Month(YMD), Day(YMD), Hours, Minutes, Seconds, 0)   
From cte;  

Tom

Answer 2

I am having trouble understanding the code below;

Me too, because that's not a valid SQL statement.
What are you trying to achieve here?

Answer 3

It is a valid SQL statement and work fine but I do not understand what it's doing and looking for a better solution

Answer 4

I don't think there will be a logically simpler code than you publish the source code, I am also looking for other ways to try to rewrite it on the Internet, the existing solution is to create a udf, but that implementation is also based on this code, and does not Universal.

Through the query, it is found that julian currently only exists in the database under IBM, DB2 has the JULIAN_DAY function, we have not heard of this special name in sql server, I will directly explain the part of this code to you below. On the premise that you know the functions of right and substring.

The first three lines of the code directly calculate the exact time 20:35:10.000 after the date,First, the first step is to concatenate 000000 and 203510 of nvchar type from the incoming variable val1 to get 000000203510, intercept 6 characters from right to left to get 203510, use the substring function to extract the characters of length 2, and get it before the first comma 20, substring (@val1, 3, 2) gets 35, substring (@val1, 5, 2) gets 10, use dec to control the precision of the output, and the date will be obtained through some operations later, you should try it yourself, This is interesting code.

As for people saying your code is incomplete, yes, it doesn't work because you define nothing and the basic syntax is wrong. Attach the correct DDL for follow up.

--DDL  
 declare @Val1 VARCHAR(6) = '203510'  
declare @Val2 VARCHAR(6) ='122189'  
SELECT dateadd(hour,cast(substring(right('000000'+cast(@Val1 as nvarchar),6),1,2) as dec(2,0)),   
     dateadd(minute,cast(substring(right('000000'+cast(@Val1 as nvarchar),6),3,2) as dec(2,0)),   
     dateadd(second,cast(substring(right('000000'+cast(@Val1 as nvarchar),6),5,2) as dec(2,0)),   
    cast(convert(varchar,  
 dateadd(dy,cast(substring(cast(@Val2+1900000 as nvarchar),5,3) as int)-1,  
     substring(cast(@Val2+1900000 as nvarchar),1,4)+'0101'),101) as datetime))))  

Bert Zhou

Answer 5

I would like to create a Function which should accept Val1 and Val2 as parameter; would it be possible?