Time in Documentum. MSSQL challenge

Interesting, when I was writing post about time in Documentum, I have noticed that storing dates in UTC timezone would cause difficulties for reporting software, but I couldn’t imagine that everything is so really bad. The problem is that among all supported databases only Oracle database works correctly with timezones, i.e. I’m able to compensate any timezone offset in SQL selects by writing something like:

all other databases do not support timezone regions out of the box and work only with specific “(-|+)HH:MM” offsets, for example, in case of MSSQL in order to work correctly with UTC time (i.e. properly handle day light saving) you will need to install T-SQL Toolbox, which is slow by design. I do believe because of this Documentum’s DATETOSTRING_LOCAL function has so weird behaviour:

i.e. Documentum translates DATETOSTRING(r_creation_date, ‘yyyy-mm-dd’) directly to SQL’s TO_CHAR(dm_sysobject.r_creation_date, ‘yyyy-mm-dd’), but it unable to do the same for DATETOSTRING_LOCAL(r_creation_date, ‘yyyy-mm-dd’), in order to do that it converts date to string preserving information about time (i.e. TO_CHAR(dm_sysobject.r_creation_date, ‘mm/dd/yyyy hh24:mi:ss’)) and then converts resulting string to requested format and server’s timezone. Due to this implementation you are unable (actually you can, but results are unreliable) to use DATETOSTRING_LOCAL function in WHERE clause and subqueries.

Now, I do think that for the most installations storing dates in UTC timezone is an evil, and enabling this feature by default was a big EMC’s mistake.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s