Data deploy- update date format is not working

Hi,

I've problem with date deploy, date format update. Running on opendeploy 6.0.2 and deploy to mysql database.
I've no issue on new file submission that contains metadata (date field). Everything was perfect. However, whenever metadata value(date field) is changed, i'll encounter data deployment error.

In short, insertion statement work perfect but not update.

From the log,

DAS: ERROR Smiley Frustrated

impleDateFormat.parse() could not parse the string '0000-00-00' with format: yyyy-MM-dd HH:mm:ss.S
DAS: ERROR:data-format attribute for the column must conform to the format documented in SimpleDateFormat class
DAS: WARNING:db-datetime-format attribute not set for [docu_effective_date] in group [document]. Assuming default: yyyy-MM-dd HH:mm:ss.S

However, i did state the date format in my dbschema.

What is wrong with my configuration?

Find more posts tagged with

Comments

Bill Klish

there is another format parameter you need to add to the field that is not really documented anywhere:

Here is a snippet from a date field we use:

<column name="start_date" data-type="DATE" data-format="MM/dd/yyyy" db-datetime-format="yyyy-MM-dd" value-from-field="start_date" allows-null="yes"/>

the db-datetime-format needs to be set for your field along with the data-format.

yoonwei

Thanks, it's working now

jkm

You have given your format yyyy-MM-dd but not yyyy-MM-dd HH:mm:ss. If you provide HH:MM Smiley Frustrated

S in your date format as well then it works fine. You can get the actual date from TS in YYYY-MM-DD format and then concatenate 00:00:00 for hh:mm:ss and deploy the record thru DB and it will work fine.

cheers
-Rocky

rodigz

Hi All,

For some reason it is not working in my system.
Basically I want to populate my database with time stamp also

config
<column name="DT_CREATED" data-type="DATE" value-from-field="dt_report" allows-null="yes" data-format="MM/dd/yyyy" db-datetime-format="yyyy-MM-dd" />

error
DD:ERROR Smiley Frustrated

impleDateFormat.parse() could not parse the string '19-January-2006' with format: MM/dd/yyyy
DD:ERROR:data-format attribute for the column must conform to the format documented in SimpleDateFormat class

thanks!

Migrateduser

If you're receiving the date as "19-January-2006", then you need to specify data-format as "dd-MM-yyyy".

Dave

Environments:
(1,2) TS6.5, TS6.5SP1 on W2K3
(3) Vignette V7 Portal on Solaris 9

rodigz

thanks but still getting this error

DD:ERROR Smiley Frustrated

impleDateFormat.parse() could not parse the string '20-January-2006' with format: dd-MM-yyyy
DD:ERROR:data-format attribute for the column must conform to the format documented in SimpleDateFormat class

Migrateduser

I'm looking at this reference:

http://java.sun.com/j2se/1.3/docs/api/java/text/SimpleDateFormat.html

which you may find helpful, but since your month is full text, this page suggests you may need to use MMM instead of MM. It's worth a shot -- there are some very strange examples provided on that page but I consider java.sun.com to be a pretty good source for this kind of info

Dave

Environments:
(1,2) TS6.5, TS6.5SP1 on W2K3
(3) Vignette V7 Portal on Solaris 9

rodigz

thanks a lot. appreciate your help.

Migrateduser

Did it work??

Environments:
(1,2) TS6.5, TS6.5SP1 on W2K3
(3) Vignette V7 Portal on Solaris 9

concatenation_test.rptdesign

sample_data.txt

rodigz

still not working..

<column name="DT_expiration" data-type="DATE" value-from-field="dt_expiration" allows-null="yes" data-format="d-MMMM-yyyy"/>

this works fine now I want to add the time stamp. do i need to change something in dct? where it will get the time stamp.
I really to know the different throught the time stamp.

thanks.

Adam Stoller

I believe data-format is the format of the source data and db-datetime-format is the format of the output data to the db:

db-datetime-format â€” specifies the format in which date and time stamp values are returned when executing a query. Not required. Value syntax is vendor-specific. For Oracle: if specified, it should be the Oracle equivalent of the data-format attribute value. SQL Server: if specified, it should be the JDK equivalent of the default format in which SQL Server returns date values. If not set, the default value is yyyy-MM-dd hh:mm:ss.S. This attribute is required for Informix Database Server. Does that help?

now I want to add the time stamp. do i need to change something in dct? where it will get the time stamp.
I really to know the different throught the time stamp.

The timestamp is added to the same attribute - have you read the documentation?
Or are you saying you want to use a timestamp as of the time of deployment? If so - I believe that is more difficult. I tried to do that at one customer site and after much trying we ended up using a DB trigger that ran on insert (or something like that - I wasn't the implementer on the DB side)

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com

Migrateduser

Well, I haven't seen your DCT so I don't know. If you're not capturing the time, then you'll need to do that. It also looks like you removed "db-datetime-format". I know you mentioned that you're on Mysql and I think Mysql is flexible enough to not require that field but I know that if I left it out of my Oracle implementation, it would yell at me!

If you wanted to add the timestamp, you'll also need to supplement d-MMMM-yyyy with the appropriate symbols (I think it's HH:m:s for a 24-hour clock but I don't have my reference in front of me now so I'm not sure -- it should be on that page I referred to earlier).

Dave

Environments:
(1,2) TS6.5, TS6.5SP1 on W2K3
(3) Vignette V7 Portal on Solaris 9

rodigz

this is getting interesting. thanks fish.

rodigz

guys..last try.

From dct I will pass the date value format 20-January-2006.

schema.
<column name="DT_expiration" data-type="DATE" value-from-field="dt_expiration" allows-null="yes" db-datetime-format ="d-MMMM-yyyy hh:mm:ss.S"/>

error.

DD:db-datetime-format is not specified for [DT_CREATED]. Using default converter.
DD:Converted JDK DateFormat [d-MMMM-yyyy] to Oracle DateFormat [DD-MONTH-YYYY].

thanks again.

Migrateduser

Another stab, but what comes after "last try"? Do you just give up or just stop this thread? Just curious...

If you're going to be populating a field that is expecting SECONDS (I assume your Mysql column is DATETIME and not DATE), you'll need to declare it as such in your schema. You still have DATE and even though you're passing only a DATE, your DB is expecting to receive DATETIME. Also, are you sure about "ss.S"? I don't believe that Mysql supports fractions of a second -- not even version 5.

If you're passing it 20-January-2006, where are you getting the time from, then? If you don't care so much about it and are okay with 00:00:00, try keeping the db-datetime-format at DATE and let Mysql assume the 00:00:00 part. It's flexible like that.

Dave

Environments:
(1,2) TS6.5, TS6.5SP1 on W2K3
(3) Vignette V7 Portal on Solaris 9

rodigz

oh.last try to this approach I need to look for other way.
thanks. I will post again if I came up with something.

RajatChopra

I'm looking at this reference:

http://java.sun.com/j2se/1.3/docs/api/java/text/SimpleDateFormat.html

which you may find helpful, but since your month is full text, this page suggests you may need to use MMM instead of MM. It's worth a shot -- there are some very strange examples provided on that page but I consider java.sun.com to be a pretty good source for this kind of info

Dave

Environments:
(1,2) TS6.5, TS6.5SP1 on W2K3
(3) Vignette V7 Portal on Solaris 9

Hey Dave,
Thanks a lot for this link. I dont know how I could have done without it.
Have been trying the DATE TIME for over a week now.

And it works now, after careful inspection of this link.

-R

Migrateduser

I also got similar error and after careful inspection what I found is that
if the record is getting updated it reads the existing value from the database and parses it using SimpleDateFormat. So your db-datetime-format must exactly specify what is stored in the format. I have seen that though I send zone information also, MySQL does not care about and still stores it as yyyy-MM-dd HH:mm:ss.

-koti
TechAspect

jude_asanka

HI Everyone this is working for sure.

[HTML]

[/HTML]