DQL - datetostring

hari_rakotomanga

Hi everybody,

When I execute the following query, it returns me a result when committe_date = 01.01.2015 00:00:00

select * from custom_document where datetostring(committee_date, 'yyyy') = '2014'

But I want only custom_document with 2014 year. I saw that datetostring_local is not possible in Where clause.

What is the solution ?

Thank you.

Find more posts tagged with

Comments

Jeremy Saumen

Hi,

You should check exactly what is the SQL query generated : dates are saved in UTF and if the document was saved from another time zone than the one you are querying, the result may be correct (disappointing but correct).

Try to execute this in API (using a superuser to bypass the security part of the query which is useless here)

query,c,select * from custom_document where datetostring(committee_date, 'yyyy') = '2014'

close,c,q0

apply,c,,GET_LAST_SQL

next,c,q0

get,c,q0,result

close,c,q0

Alvaro_de_Andres

Dates are stored in UTC and converted to your local time when retrieved, however, when you use DATETOSTRING you're not returning a date, but a String, so the conversion doesn't happen. That's why you're getting the UTC date, and that's why DATETOSTRING_LOCAL was implemented

Also, if you read the DQL guide:


DATETOSTRING
The DATETOSTRING function returns a date as a character string in UTC in a particular format.


DATETOSTRING_LOCAL
The DATETOSTRING_LOCAL function formats a date value to a character string in the server local
time. This function always expresses the result in the server local time.

hari_rakotomanga

In generated SQL, I have:

TO_CHAR(custom_document.committee_date, 'yyyy')='2014'

Alvaro_de_Andres


Alvaro de Andres wrote:



when you use DATETOSTRING you're not returning a date, but a String


hari_rakotomanga wrote:

In generated SQL, I have:

TO_CHAR(custom_document.committee_date, 'yyyy')='2014'

hari_rakotomanga

Hi,

So what is the query that should I use to get that I want ?

Thank you

Alvaro_de_Andres

select * from custom_document where DATETOSTRING_LOCAL(committee_date, 'yyyy') = '2014'

hari_rakotomanga

The DATETOSTRING_LOCAL function does not work in Where clause.

External121


DATETOSTRING_LOCAL
The DATETOSTRING_LOCAL function formats a date value to a character string in the server local
time. This function always expresses the result in the server local time.

This solved an issue I was having! Thank you