0

I have an input date in format 2017-07-27T10:00:00Z which needs to be converted into JDE(117208), can anyone help me how to do it in SQL or XSLT. I really appreciate your help.

I need a SQL Query for the same

input format : 2017-07-27T10:00:00Z output format : 117208

michael.hor257k
  • 113,275
  • 6
  • 33
  • 51
Gnaneshwar
  • 15
  • 1
  • 9

4 Answers4

0

Here is the query you need.

SELECT TO_CHAR(CAST(TO_TIMESTAMP_TZ(REPLACE('2017-07-27T10:00:00Z', 'T', ''), 'YYYY-MM-DD HH:MI:SS TZH:TZM') AS DATE), 'J') as JULIAN
FROM    dual
JRG
  • 4,037
  • 3
  • 23
  • 34
0

simple you can do this in one line:

select dat, concat(datediff(year,'1900-1-1',dat) /100,datediff(year,'1900-1-1',dat) % 100, datepart(DAYOFYEAR,dat) ) as JDE from d

I stored your example input data in a table call d under column dat:

dat        JDE
---------- ------------------------------------
2017-07-27 117208

Explanation:

first it is important to understand what is JDE julian date. it is in this format CYYDDD:

  • C stands for centuries and it starts at the 20th century (hence the hard coded date 1900-01-01 in my query). C is 0 if the years of 19xx. C is 1 for the years of 2000. This is why we took your date and get the number of different years since 1900-01-01 and divided it by 100 to get the number of centuries. datediff(year,'1900-1-1',dat) /100
  • YY stands for the years in the century. so if the year is 2089 the first three digit of JDE would be 189DDD datediff(year,'1900-1-1',dat) % 100 helps you get the remainder of the century which is the years.
  • DDD is the day of the year. In sql server there is a very nice to get day of the year using datepart(DAYOFYEAR,dat)

Now we get all three parts of JDE we concat() them together and you have your JDE year.

EDIT:

I added some logic to ensure all fields would have proper 0 paddings.

SELECT dat ,CONCAT(RIGHT(CONVERT(varchar(1),datediff(year,'1900-1-1',dat) / 100),1) , RIGHT(CONVERT(varchar(2), datediff(year,'1900-1-1',dat) % 100),2), RIGHT('000' + CONVERT(varchar(3),datepart(DAYOFYEAR,dat)),3)) from d
OLIVER.KOO
  • 5,654
  • 3
  • 30
  • 62
  • can you please make it clear. i really appreciate your help. – Gnaneshwar Jul 26 '17 at 21:18
  • I think the expected output is JDE date – Gnaneshwar Jul 26 '17 at 21:44
  • ahhh that makes more sense. I think I still know how to do this. Let me work on it and I will update my answer when I am done. – OLIVER.KOO Jul 26 '17 at 21:46
  • I updated it and I will add some explanations to it. In the mean time see if it worked for you – OLIVER.KOO Jul 26 '17 at 21:53
  • I'm not so perfect in SQL. I really appreciate if you can make it clear. – Gnaneshwar Jul 26 '17 at 21:59
  • just substitute dat with the date you desire to convert. If you are using this for a one time use. you can put your date in the query. `select concat(datediff(year,'1900-1-1','2017-07-27') /100,datediff(year,'1900-1-1','2017-07-27') % 100, datepart(DAYOFYEAR,'2017-07-27') ) as JDE ` but I wouldn't recommand doing that – OLIVER.KOO Jul 26 '17 at 22:12
  • I executed above SQL statement but its throwing an error ;) – Gnaneshwar Jul 26 '17 at 22:20
  • Invalid characters – Gnaneshwar Jul 26 '17 at 22:24
  • are you using sql server? did you executed the exact query I posted above? It works fine for me? and are you doing this for one time use? or you have a table of dates you want to convert to JDE? When you comment please give as much information as possible so I can help you debug. – OLIVER.KOO Jul 26 '17 at 22:27
  • I know you are using SQL. but which DBMS implementation are you using? MySQL? Oracle? MS SQL Server? they have different syntax that is could by why you are getting an error. When you ask a sql related question remember to tag the DBMS implementation you are using. Also I encourage you to try to understand the query yourself, my explanation should help you understand it. If you can understand it then you can further modify or substitute the query to get your answer. Don't expect you can copy and past code from SO and have people do your work for you. – OLIVER.KOO Jul 26 '17 at 22:35
  • Oracle we are u – Gnaneshwar Jul 26 '17 at 22:42
  • The syntax I used in my code is sal server. Just go ahead change the syntax to use Oracle for the parts needed. And what do you mean by "we r u"? – OLIVER.KOO Jul 26 '17 at 23:57
0

It seems that the date format used by JDE, namely CYYDDD, is often referred to within the JDE user community as a Julian date, but it actually has nothing to do with Julian day numbers as the term is commonly understood in the rest of the world. (And furthermore, just to be quite clear, Julian day numbers have nothing to do with the Julian calendar.)

When used correctly, a Julian day number refers to a 24-hour period starting at noon, though I'm sure that doesn't apply to JDE dates.

So you have confused everyone by talking of Julian dates rather than JDE dates.

In XSLT 2.0 you can get the last five characters of the JDE date using

format-date($date, '[Y01][d001]')

But for the first character you'll need custom logic:

if ($date lt xs:date('2000-01-01')) then '0' else '1'

or if you prefer,

year-from-date($date) idiv 100 - 19

which has the advantage of working beyond 2100.

Michael Kay
  • 156,231
  • 11
  • 92
  • 164
0

You can try.

select DATEPART(yyyy,GETDATE())*1000+DATEPART(DAYOFYEAR,GETDATE())-1900000

Date format should be (YYYY-MM-DD)

Siddhesh
  • 871
  • 10
  • 15