IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

Need to retrieve Julian_date


 
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Suganya87

New User


Joined: 09 May 2016
Posts: 12
Location: India

PostPosted: Wed Aug 17, 2016 7:27 pm
Reply with quote

Hi ,

I need ( Year + Today's Julian date ) for an example if Julian date of today is 230 then I need 2016230 as my output.

I tried Julian_Day but it gave integer value from 1472 B.C .

Can anyone tel me how to achieve this in SQL query
_________________
TIA

Read more: Use [URL] BBCode for Links
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2431
Location: Hampshire, UK

PostPosted: Wed Aug 17, 2016 7:48 pm
Reply with quote

Get the julian day of the last day of the previous year and that of today. Subtract one from the other. Concatenate with the year. Unlr=ess there is a function to do it - check the index of the manual.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1348
Location: Israel

PostPosted: Wed Aug 17, 2016 7:54 pm
Reply with quote

Then check documentation for VARCHAR_FORMAT function.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2503
Location: NY,USA

PostPosted: Thu Aug 18, 2016 9:26 pm
Reply with quote

VARCHAR_FORMAT - J (Integer format too)- Julian day (number of days since January 1, 4713 BC). Try this,
Code:
SELECT concat(year(current date),DAYS(CURRENT_TIMESTAMP) -
 DAYS(SUBSTR(CHAR(CURRENT_TIMESTAMP),1,4)||'-01-01') + 1)
 FROM SYSIBM.SYSDUMMY1
or this,
Code:
select
 CAST(TRIM(CHAR(YEAR(current date)))||
REPEAT('0',(3-LENGTH(TRIM(CHAR(DAYOFYEAR(current date))))))||
TRIM(CHAR(DAYOFYEAR(current date))) AS CHAR(7))
from sysibm.sysdummy1
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Aug 22, 2016 3:08 pm
Reply with quote

Code:
varchar_format(current_date,'YYYYDDD')
if you have a recent version of DB2.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> DB2
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts Retrieve multiple records with metaco... CA Products 0
No new posts SQL to retrieve latest Eff date and then DB2 8
No new posts Accessing control blocks to retrieve ... COBOL Programming 5
No new posts CICS RETRIEVE CICS 2
No new posts Retrieve current year or month using ... DFSORT/ICETOOL 15

Back to Top