How to get substr of a date

giasqui

Hi,

I'm implementing a query form with a date field as this:

and revision_date=date('$value(revision_date)','mm/dd/yyyy hh:mm:ss')

When I execute the query from the search widget, the query doesn't works because in the log I found this:

and revision_date=date('02/07/2022 UTC','mm/dd/yyyy hh:mm:ss')

So, the system add UTC and the date format fail.

How I can do a substr on date in order to have this?

and revision_date=date('02/07/2022','mm/dd/yyyy hh:mm:ss')

I have tried:

and revision_date=date('substr($value(revision_date),1,10)','mm/dd/yyyy hh:mm:ss')

but it doesn't work.

Thanks.

Regards.

Find more posts tagged with

Comments

DCTM_Guru

Can you provide example of what you are trying to do? There is dateadd() function in DQL that might be helpful.

giasqui

Behind the scenes, in the DQL of a query form I have this:

select object_name,eni_title,document_type_desc,discipline_desc,revision,revision_date,r_modify_date,r_modifier,intelligent_url from eni_job_doc where revision_date=date('$value(revision_date)','mm/dd/yyyy hh:mm:ss');

On the property page I have a calender related to the revision_date. If I select on this calendar today date 09/02/2022, after I launch the "run query", behind the scenes the systems send this value '02/08/2022 23:00:00 UTC' insted of '09/02/2022'.

Then the query in the query form become:

and of course it fails because UTC is not needed.

Do you have any idea about this behavior?

Thanks.

Hicham Bahi

This is indeed annoying but your query doesn't make much sense: you almost never use the equal operator to compare a date up to the second because you're unlikely to find anything. Usually you use a date range.

giasqui

I know batcham but in our scenario the revision date is always with 00:00:00. Indeed the following query runs on dqman:

because I have objects with "08/02/2022 00:00:00" as revision date.

Could be the UTC added from the calendar a bug?

Hicham Bahi

What D2 version are you on? Just found this in the 21.4 release notes under fixed issues: DTWO-76573: UTC keyword & date value resolution is not consistent when retrieving the date fields in the query form.

Might be worth trying it with 21.4.

DCTM_Guru

Not familiar with how D2 handles timezone offsets, but take a look at this blog entry. It explains why UTC is used by server AND not by the client and how to account for it:

The Content Server and Time Zones | dm_misc: Miscellaneous Documentum Information (wordpress.com)

giasqui

Thanks both. I will check. I'm working on 16.5 ver.

giasqui

I've modified r_tz_aware, r_normal_tz and also the dfc.time_zone in the dfc.properties but after a server restart nothing change.

DCTM_Guru

Are you on the latest service pack for 16.5? If so, I would also submit a ticket to support to see if there is hotfix for this issue.

giasqui

vD2 16.5.1040 build 0069

Thanks

Hicham Bahi

Attributes starting with r_ are read-only: you are not supposed to modify them. Did you check if the value was kept after restart?

Generally, there should be a way to set them programmatically (i.e. DFC/iapi) or some other way (e.g. server.ini although that's more for dm_server_config object). Never tried myself. Perhaps you can open a ticket with support.

giasqui

Yes bacham the values was kept after restart. If I remember well, r_ stands for repeating attributes.

Thanks.

Hicham Bahi

No. r_ stands for read-only like I wrote. Such attributes are generally set by the server as a response to an API call (e.g. r_object_id or r_creation_date). Some can be set by superuser but usually not recommended.

DCTM_Guru

@Squiccimarro_Giacomo - repeating value attribute are stored in corresponding "..._r" table (vs "_s" for single value attribute).

To see if this is an issue with D2, I would set the revision_date to today using DA and see if you are seeing the same behavior.

giasqui

Ok.

What do you mean with "I would set the revision_date to today using DA"?

What I have to do in the DA and where?

DCTM_Guru

Find a test document and run this DQL:

update eni_job_doc object set revision_date=date(now()) where r_object_id='...'

Then, run your select DQL against this document and see what value it is returning. This will validate if the date offset is set correctly and if this issue is specific to D2.

giasqui

Done.

update eni_job_doc object set revision_date=date(now) where r_object_id='0902814a804983e0';

The result is:

10/02/2022 15:25:31

DCTM_Guru

So it looks like the date format is not specific just to D2. Have you tried just changing your query to reflect the other date format:

eni_job_doc where revision_date=date('10/02/2022 00:00:00','dd/MM/yyyy hh:mm:ss') ;

giasqui

If you mean the query in the query form, yes, I tried and it does not works. :-(

DCTM_Guru

Looking back to your original post, why are you searching for specific revision date (ie using =)? Try using greater than or equal to AND less than:

where revision_date>=date('10/02/2022 00:00:00','dd/MM/yyyy hh:mm:ss') AND revision_date<date('10/03/2022 00:00:00','dd/MM/yyyy hh:mm:ss')

Notice that the date value is one more day of the value submitted for less than criterion.

giasqui

I'm using = because revision_date are always like 10/02/2022 00:00:00. I mean that the time is always set to 00:00:00 and customer would like to search for date and not for range.

DCTM_Guru

So it sounds like you are letting D2 generate your DQL. If that's the case, then I think you need to contact support to see if there is fix to your issue.

giasqui

Yes, it is. I will open the ticket to the support. Thanks for all!