DATE Convert on Oracle

Hi!

We're currently storing a set of dates (publish & expiration) in our DCR's and sending them tho Oracle via DataDeploy.
All our dates are vaildated upon entry for our correct format (yyyy-MM-dd), as well as our DD configs check for the same format ( see below):

-- DD line sample start----
<column name="PUBDATE" data-type="DATE" data-format="yyyy-MM-dd" value-from-field="publish_date" is-replicant="no" allows-null="yes" />
<column name="EXPDATE" data-type="DATE" data-format="yyyy-MM-dd" value-from-field="expiration_date" is-replicant="no" allows-null="yes" />
-- DD line sample end ---

Bascially, for example, if our stored date in the DCR is 2004-12-31, the database query returns 01/25/2004. This is obviously the wrong format, but more importantly, it seems the month is getting a '01' instead of the proper month.

I've noticed the DD output mentions no date-time format specified, but like I said, we've never seen this come out before. Would adding a format specification sove this, and if so is this done on our DD confi or on the Oracle table definition?

We've never had this error before, but now when DD runs, it spuits out the following (I've cut all the irrelevant fields to make this brief...)

-- DD output start ----
db-datetime-format is not specified for [PUBDATE]. Using default converter.
Converted JDK DateFormat [yyyy-MM-dd] to Oracle DateFormat [YYYY-MM-DD].
db-datetime-format is not specified for [EXPDATE]. Using default converter.
Converted JDK DateFormat [yyyy-MM-dd] to Oracle DateFormat [YYYY-MM-DD].
SELECT Smiley Frustrated

ELECT ANNTYPE,TO_CHAR(PUBDATE,'YYYY-MM-DD') AS PUBDATE,TO_CHAR(EXPDATE,'YYYY-MM-DD') AS EXPDATE,ANNTITLE,FILNAME,ANNID FROM RSSP_ANNOUNCEMENTS WHERE ANNID = ?
Recurse select: pIndex = 0, value = 1496
BuildTuples:executeQuery returned valid result set
TTableSchemaHelper object for [RSSP_ANNOUNCEMENTS] found in cache.
BuildTuples:BuildRows returned: true
DeleteForUpdate: skipping root group
TTableSchemaHelper object for [RSSP_ANNOUNCEMENTS] found in cache.
TTableSchemaHelper object for [RSSP_ANNOUNCEMENTS] found in cache.
db-datetime-format is not specified for [PUBDATE]. Using default converter.
Converted JDK DateFormat [yyyy-MM-dd] to Oracle DateFormat [YYYY-MM-DD].
db-datetime-format is not specified for [EXPDATE]. Using default converter.
Converted JDK DateFormat [yyyy-MM-dd] to Oracle DateFormat [YYYY-MM-DD].
SelectCurrentRowsUsingPath: SELECT ANNTYPE,TO_CHAR(PUBDATE,'YYYY-MM-DD') AS PUBDATE,TO_CHAR(EXPDATE,'YYYY-MM-DD') AS EXPDATE,ANNTITLE,FILNAME,ANNID FROM RSSP_ANNOUNCEMENTS WHERE ANNID = ? ORDER BY ANNID
SelectCurrentRowsUsingPath: 1 rows selected.
DoesRowExist Smiley Frustrated

ELECT COUNT(*) FROM RSSP_ANNOUNCEMENTS WHERE ANNID = ? AND ANNTYPE = ? AND PUBDATE = ? AND EXPDATE = ? AND
Column: PUBDATE, field: publish_date, Index: 3,Converting '2004-11-27' to TIMESTAMP
Column: EXPDATE, field: expiration_date, Index: 4,Converting '2004-12-31' to TIMESTAMP

UPDATE RSSP_ANNOUNCEMENTS SET PUBDATE = ? , EXPDATE = ? WHERE AND PUBDATE = ? AND EXPDATE = ?

Column: PUBDATE, field: publish_date, Index: 2,Converting '2004-11-27' to TIMESTAMP
Column: EXPDATE, field: expiration_date, Index: 3,Converting '2004-12-31' to TIMESTAMP

Column: PUBDATE, field: publish_date, Index: 8,Converting '2004-01-25' to TIMESTAMP
Column: EXPDATE, field: expiration_date, Index: 9,Converting '2004-01-30' to TIMESTAMP
--- DD Output End ----

Any help would be greatly appreciated!
Thanks in advance!

-Will

Find more posts tagged with

Comments

Migrateduser

Hi,
I am little confused with the schema shown from the config file, the logs and the results from your sql statements.

Here is my sample config file and test data.

Let me know if this helps. If it dose not then..

Could you provide :
your configuration file, test data, DD version, Oracle version.

thanks
srini

Jens

Hi,

i'm not a database expert, but i think you can configure the database-date-output in the database otherwise use to_char('yyyy-mm-dd',DATEFIELD).
also you wrote 2004-12-31input gives a 01/25/2004 output, but there is also a input of that date
Column: PUBDATE, field: publish_date, Index: 8,Converting '2004-01-25' to TIMESTAMP
maybe there is only something wrong with the output-query?

Ciao,
Jens

WillyRocks

Well we finally solved our issue, but not exactly in the way we hoped. It seems that for some unknown (as of yet) reason, DD looks like it was losing sync between the DCR ID's and the DB's Key ID's.

Since the issue was only happening on a Development installation, we cleared out all existing DCR's and re-ran our workflows and now DD seems to be deploying the data correctly to the DB. We are now also getting the DATE directly from the DCR in question rather than looking up the value from another DD0linked table to minimize further data sync problems.

Anyways, thanks to all who answered the post!1 Smiley Happy

-Will