How to INSERT a julian date in a DATE column ?

Di Franco Vincenzo

How to INSERT a julian date in a DATE column ?
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.



Hessel Rus

Re: How to INSERT a julian date in a DATE column ?
(in response to Di Franco Vincenzo)
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



Terry Purcell

Re: How to INSERT a julian date in a DATE column ?
(in response to Hessel Rus)
If you want the date as CCYYDDD, then you can use an insert statement in the
following way:

INSERT INTO MYTABLE
VALUES (:col1, :col2, YEAR(CURRENT DATE) * 1000 + DAYOFYEAR(CURRENT DATE))

This implies at least V6 on OS/390 which introduced the DAYOFYEAR function
and expressions in the VALUES clause of an INSERT.

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 8:08 AM
To: [login to unmask email]
Subject: How to INSERT a julian date in a DATE column ?


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.