DATETOSTRING function returns wrong date

pakorn

I have tried DQL statement below on DA and result from DATETOSTRING function is difference from original date.

r_creation_date is time at GMT+7 but DATETOSTRING is GMT.

Is there any way to make result from DATETOSTRING to same timezone with original date?

Thanks

Pakorn

DQL :

SELECT r_creation_date,DATETOSTRING(r_creation_date,'DD/MM/YYYY hh')

FROM dm_document

Timezone:

- CS host timezone: GMT+7

- DA host timezone: GMT+7

Find more posts tagged with

Comments

DCTM_Guru

How are you planning to use the DQL results? The reason I am asking is that Webtop/WDK app already can handle the timezone offset. Once you configure the app server properly, all date/time values will be "corrected" when displayed to the user. This is not the case with DQL, since it is just returning the value that is stored in content server.

pakorn

My plan is use in DQL selection in taskspace search template, to change date format of column from 'MM/dd/yyyy' to 'dd/MM/yyyy'.

But I've tried adding -Duser.timezone=GM+7:00 and -Duser.timezone=Asia/Bangkok in Tomcat java option and it does not work.

I also try by adding dfc.time_zone=GMT+07:00 and dfc.time_zone=Asia/Bangkok too but DATETOSTRING still return date at GMT not GMT+7.

Thanks

Pakorn

DCTM_Guru

"...My plan is use in DQL selection in taskspace search template, to change date format of column from 'MM/dd/yyyy' to 'dd/MM/yyyy'."

- You should able to change this by changing the date/time format on the server.

pakorn

Solution is editing app.xml to in TaskSpace custom folder.

add:

<default_locale>th_TH</default_locale>

for change date format.

<fallback_to_english_locale>true</fallback_to_english_locale>

for fallback to english locale.

Thanks

Pakorn

josh_uhg

Don't know if you're still looking for an answer, since this is a pretty old post, but DATETOSTRING_LOCAL should give you what you're looking for.