Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need to retrieve Julian_date

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Need to retrieve Julian_date
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: http://ibmmainframes.com/viewtopic.php?p=336533#336533#ixzz4Hb7h1dTT
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1717
Location: UK

PostPosted: Wed Aug 17, 2016 7:48 pm    Post subject:
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: 1219
Location: Israel

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

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

Senior Member


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

PostPosted: Thu Aug 18, 2016 9:26 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Mon Aug 22, 2016 3:08 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Retrieve current year or month using ... vnktrrd DFSORT/ICETOOL 15 Tue Mar 15, 2016 4:14 pm
No new posts retrieve line commands from the data ... Pedro TSO/ISPF 6 Sat Oct 17, 2015 5:41 am
No new posts DFSORT - Retrieve selective records f... narasimha_devi DFSORT/ICETOOL 8 Thu Aug 06, 2015 4:12 pm
No new posts Retrieve records in the order of alte... jacobdng Compuware & Other Tools 8 Fri Jul 31, 2015 8:16 am
No new posts Retrieve Job ID and return code in JCL mfguy01 JCL & VSAM 2 Mon Mar 23, 2015 5:03 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us