R: How to INSERT a julian date in a DATE column ?

Di Franco Vincenzo

R: How to INSERT a julian date in a DATE column ?
Is it possible also in DB2 v5 ?
Bye.

-----Messaggio originale-----
Da: DB2 Data Base Discussion List [mailto:[login to unmask email]Per
conto di Hessel Rus
Inviato: lunedì 30 dicembre 2002 15.58
A: [login to unmask email]
Oggetto: Re: How to INSERT a julian date in a DATE column ?


If you want to insert an juliandate into a date columns just use this:

INSERT INTO <table> ( <date_col>, other_cols) VALUES ( DATE('juldate' e.g
'2002364'), other_values)

The juliandate is transformed into a regular date (2002-12-30 in this case)
and can be transformed back into a juldat via:

SELECT ( YEAR(<date_col>)*1000+DAYOFYEAR(<date_col>) AS JULDATE (in
numeric format) or inte
SELECT CAST ( YEAR(<date_col>)*1000+DAYOFYEAR(<date_col>) as char(7)) AS
JULDATE (in character format)

Hope this answers your question
Hessel




Di Franco
Vincenzo To:
[login to unmask email]
<[login to unmask email] cc:
BCC.IT> Subject: How to INSERT a
julian date in a DATE column ?
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
LASSOC.COM>


12/30/2002 03:07
PM
Please respond to
DB2 Data Base
Discussion List






Hi all,
I need to make an SQL INSERT specifying for a DATE column a julian date.
But seem not possible to use any function (DATE) during insert so ...
TIA. Bye.



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can








Hessel Rus

Re: R: How to INSERT a julian date in a DATE column ?
(in response to Di Franco Vincenzo)
The "forward" transformation (juldate ==> date) i think is was possible in
V5. Just try is with SELECT DATE('2002364') FROM sysibm.sysdummy1.
But the "backward" transformation is not possible since DAYOFYEAR (and also
CAST) is not available in V5.





Di Franco
Vincenzo To: [login to unmask email]
<[login to unmask email] cc:
BCC.IT> Subject: R: How to INSERT a julian date in a DATE column ?
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
LASSOC.COM>


12/30/2002 04:14
PM
Please respond to
DB2 Data Base
Discussion List






Is it possible also in DB2 v5 ?
Bye.

-----Messaggio originale-----
Da: DB2 Data Base Discussion List [mailto:[login to unmask email]Per
conto di Hessel Rus
Inviato: lunedì 30 dicembre 2002 15.58
A: [login to unmask email]
Oggetto: Re: How to INSERT a julian date in a DATE column ?


If you want to insert an juliandate into a date columns just use this:

INSERT INTO <table> ( <date_col>, other_cols) VALUES ( DATE('juldate' e.g
'2002364'), other_values)

The juliandate is transformed into a regular date (2002-12-30 in this case)
and can be transformed back into a juldat via:

SELECT ( YEAR(<date_col>)*1000+DAYOFYEAR(<date_col>) AS JULDATE (in
numeric format) or inte
SELECT CAST ( YEAR(<date_col>)*1000+DAYOFYEAR(<date_col>) as char(7)) AS
JULDATE (in character format)

Hope this answers your question
Hessel




Di Franco
Vincenzo To:
[login to unmask email]
<[login to unmask email] cc:
BCC.IT> Subject: How to INSERT a
julian date in a DATE column ?
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
LASSOC.COM>


12/30/2002 03:07
PM
Please respond to
DB2 Data Base
Discussion List






Hi all,
I need to make an SQL INSERT specifying for a DATE column a julian date.
But seem not possible to use any function (DATE) during insert so ...
TIA. Bye.


visit
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can


visit
the




visit
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can





Terry Purcell

Re: How to INSERT a julian date in a DATE column ?
(in response to Hessel Rus)
INSERT INTO MYTABLE
SELECT :col1, :col2,
YEAR(CURRENT DATE) * 1000
+ DAYS(CURRENT DATE)
- DAYS(SUBSTR(DIGITS(YEAR(CURRENT DATE - 1 YEAR)),7,4) CONCAT '-12-31')
FROM SYSIBM.SYSDUMMY1

Messy but effective in V5.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Di Franco Vincenzo
Sent: Monday, December 30, 2002 9:14 AM
To: [login to unmask email]
Subject: R: How to INSERT a julian date in a DATE column ?


Is it possible also in DB2 v5 ?
Bye.

-----Messaggio originale-----
Da: DB2 Data Base Discussion List [mailto:[login to unmask email]Per
conto di Hessel Rus
Inviato: lunedì 30 dicembre 2002 15.58
A: [login to unmask email]
Oggetto: Re: How to INSERT a julian date in a DATE column ?


If you want to insert an juliandate into a date columns just use this:

INSERT INTO <table> ( <date_col>, other_cols) VALUES ( DATE('juldate' e.g
'2002364'), other_values)

The juliandate is transformed into a regular date (2002-12-30 in this case)
and can be transformed back into a juldat via:

SELECT ( YEAR(<date_col>)*1000+DAYOFYEAR(<date_col>) AS JULDATE (in
numeric format) or inte
SELECT CAST ( YEAR(<date_col>)*1000+DAYOFYEAR(<date_col>) as char(7)) AS
JULDATE (in character format)

Hope this answers your question
Hessel




Di Franco
Vincenzo To:
[login to unmask email]
<[login to unmask email] cc:
BCC.IT> Subject: How to INSERT a
julian date in a DATE column ?
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
LASSOC.COM>


12/30/2002 03:07
PM
Please respond to
DB2 Data Base
Discussion List






Hi all,
I need to make an SQL INSERT specifying for a DATE column a julian date.
But seem not possible to use any function (DATE) during insert so ...
TIA. Bye.



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can