cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle timestamp field insert row

Former Member
0 Kudos

I created an oracle table with a timestamp data type field in it. When I test the query template that inserts the row, I set the date field with a parameter value of "19-SEP-2007 10:02:00" and it works fine.

When I set the Param value in my javascript code to the same value, I get a sql insert error telling me the column length is to long.

Does anyoen have any ideas?

Accepted Solutions (0)

Answers (2)

Answers (2)

jcgood25
Active Contributor
0 Kudos

BJ,

You can also use [SD] or [ED] instead of [Param.1], and set the associated StartDate or EndDate query property in the same string format as the DateFormat (default is MM/dd/yyyy HH:mm:ss). This way the datetime string will be handled by the data server properties for DatePrefix, DateSuffix, and InternalDateFormat.

SQLServer is simpler because it takes about any string that looks like a datetime wrapped with single quotes, but Oracle likes something such as TO_DATE('09/19/2007 10:02:00','MM/DD/YYYY HH24:MI:SS')

By using one of the date tokens the data server itself will take care of the replacement so something like:

INSERT INTO TABLE VALUES([SD])

will actually go to the database as:

INSERT INTO TABLE VALUES(TO_DATE('09/19/2007 10:02:00','MM/DD/YYYY HH24:MI:SS'))

The only gotcha is that you need to make sure that the DateFormat setting for the query template is how you provide the real date value. In the query template editor, select your desired date from the calendar icon and then change the dateformat drop down (notice that the start date you selected changes format accordingly).

Then if you are using html and Javascript on the client side to provide this date field to the query object it is as simple as document.APPLETNAME.getQueryObject().setStartDate(XXXX); or document.APPLETNAME.getQueryObject().setEndDate(XXXX);

An additional trick is that if you use the [ED] token and provide no values for the time settings it will default to the current time on the webserver when the query is processed. You can see this happen in an editor test by clearing out the StartDate and EndDate properties and issuing your query as: INSERT INTO TABLE VALUES([ED])

Regards,

Jeremy

Former Member
0 Kudos

Try TO_TIMESTAMP('[Param.1]') in your query template. That will convert the passed string to the timestamp format which includes fractional seconds too.