|
View previous topic :: View next topic
|
| Author |
Message |
Lenzbanu
New User
Joined: 08 Mar 2005
Posts: 2
|
|
|
|
| can anyone suggest me how to make Julian to gregorian conversion with sql query and vice versa. |
|
| Back to top |
|
 |
Lenzbanu
New User
Joined: 08 Mar 2005
Posts: 2
|
|
|
|
To convert from gregorian to julian date, i tried the following
select date('2009-01-01') as dt,
year('2009-01-01') * 1000 + dayofyear('2009-01-01') as dt1
from temp1;
require suggestion? |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
Don't know about better/other solutions, but that does the work:
| Code: |
SELECT DATE('2009-07-08') AS DT,
YEAR('2009-07-08') * 1000 + DAYOFYEAR('2009-07-08') AS DT1
FROM SYSIBM.SYSDUMMY1 |
| Ketan Varhade wrote: |
| This save the MIPS |
Forget about the MIPS, better save the whales! |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
a date can be generated by adding the julian DDD - 1 to Jan, 01 of the julian YY.
a julian date can be generated by using the difference in days - 1 between a date and Jan 01 of the year
marso, you posted while I was composing. yours is a better solution, |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
Hi Marso,
This query wont be dynamic and needs to be changed every time. If the requirement is to run on the SPUFI,QMF then fine but for runing in the embeded COBOL-DB2 then the above mentioned logic can be right.
Correct me if I am wrong.
Thanks
Ketan Varhade |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Ketan Varhade
replace the hardcoded date with a host variable containing a db2 date datatype value.
you have been corrected. |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
| Thanks |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
Lenzbanu provided both question and answer from the beginning.
I did nothing but correct the FROM, run it under QMF and check the result.
| Quote: |
| replace the hardcoded date with a host variable |
that should have been obvious to everybody. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| that should have been obvious to everybody |
Marso, you forget where you are. |
|
| Back to top |
|
|
|
|
