|
View previous topic :: View next topic
|
| Author |
Message |
Suganya87
New User
Joined: 09 May 2016
Posts: 12
Location: India
|
|
|
|
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: ibmmainframes.com/viewtopic.php?p=336533#336533#ixzz4Hb7h1dTT |
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| 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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| Then check documentation for VARCHAR_FORMAT function. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
| varchar_format(current_date,'YYYYDDD') |
if you have a recent version of DB2. |
|
| Back to top |
|
|
|
|
