Time conversion issue

scotte over 9 years ago

We have a field added to Oracle table as a Date type. When I select this field using the Query Database it displays the field 1 day less than how it is stored in database. For example; on the table it is "11-NOV-00", when I select it displays in variable as "11/10/2000 7:00PM", which is 1 day less. Also the date inserted into the table is correct, but when retrieve it is wrong. Does anyone know how this is happening? Also, the data type of the field is "Date Time" in the smart service properties output data tab. You can't change this... it gets that type from the database. Thanks!...

Discussion posts and replies are publicly visible

Parents

0 Mike Schmitt over 9 years ago

In addition to what others are suggesting, you might want to get familiar with the local() function and similar. For instance on my project we found it helpful to create a global "toLocalDate" rule which takes a datetime (presumably from the UTC-oriented DB entry) and returns a plain date, accounting for the timezone conversion. That function contains this snippet of code (I also recommend you check the RI for null values first):

todate(
local( ri!dateTime, timezoneid() )
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Mike Schmitt over 9 years ago

In addition to what others are suggesting, you might want to get familiar with the local() function and similar. For instance on my project we found it helpful to create a global "toLocalDate" rule which takes a datetime (presumably from the UTC-oriented DB entry) and returns a plain date, accounting for the timezone conversion. That function contains this snippet of code (I also recommend you check the RI for null values first):

todate(
local( ri!dateTime, timezoneid() )
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data