Find Date Difference in hours

Hi ,

I want to find out folder that are created 1 hour before current date . So i tried the below DQL function , but giving invalid syntax at 'hours'

select r_object_id,r_creation_date from dm_folder where folder('/UN/Edu/test',descend) and DATEDIFF(hours,r_creation_date,DATE(NOW)) > 1

When i try with day instead of hours , it works fine like DATEDIFF(day,r_creation_date,DATE(NOW)) > 1

How do i get date difference in Hours .

Any ideas please ..

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

manishanandswami

Try this DQL ...

this will definitely work

select r_object_id,r_creation_date ,DATEDIFF(day,r_creation_date,DATE(NOW)) from dm_folder where folder('/UN/Edu/test',descend) and DATEDIFF(day,r_creation_date,DATE(NOW)) <= .042

============

Above query will work ..

1/24 =0 in Documentum DQL thats why you are not getting any result what the DQL suggest by Johnny GEE

Or you can try follwoing DQL

select r_object_id,r_creation_date ,DATEDIFF(day,r_creation_date,DATE(NOW)) from dm_folder where folder('/UN/Edu/test',descend) and DATEDIFF(day,r_creation_date,DATE(NOW)) <= (1.0/24)

All comments

VHK

DATEDIFF(date_part, date1, date2)

The date_part argument defines the units in which the return value is

expressed. Valid values are

year, month, week, and day.

DCTM_Guru

Try:

select r_object_id,r_creation_date from dm_folder where folder('/UN/Edu/test',descend) and DATEDIFF(day,r_creation_date,DATE(NOW)) <= (1/24)

smadduri

HI Johnny,

I tried your query

select r_object_id from dm_folder where folder('/UN/Edu/test/',descend) and DATEDIFF(day,r_creation_date,DATE(NOW)) <= (1/24)

But it does not return any records even i have folder created today 1hr back and long back as well .

I want to get folders that are created 1 hourr back from current date .

manishanandswami

Try this DQL ...

this will definitely work

select r_object_id,r_creation_date ,DATEDIFF(day,r_creation_date,DATE(NOW)) from dm_folder where folder('/UN/Edu/test',descend) and DATEDIFF(day,r_creation_date,DATE(NOW)) <= .042

============

Above query will work ..

1/24 =0 in Documentum DQL thats why you are not getting any result what the DQL suggest by Johnny GEE

Or you can try follwoing DQL

select r_object_id,r_creation_date ,DATEDIFF(day,r_creation_date,DATE(NOW)) from dm_folder where folder('/UN/Edu/test',descend) and DATEDIFF(day,r_creation_date,DATE(NOW)) <= (1.0/24)

manishanandswami

becuase in DQL

1/2 = 0 (int value)

and 1.0/2 = .5

VHK

Thanks Manish,

Good Catch and Helpful..

On Thu, Jan 17, 2013 at 5:44 PM, manishanandswami <

DCTM_Guru

Darn decimal pt. Thanks for correction

smadduri

Thanks Manish ,It works ..

VHK

Please mark the post as answered so that others can refer this in future

Thanks,

ArunSelvaraj

Hi All,

There is no diff between Johnny Gee Query and manishanandswami Query

select r_object_id,r_creation_date from dm_folder where folder('/UN/Edu/test',descend) and DATEDIFF(day,r_creation_date,DATE(NOW)) <= (1/24)

and

select r_object_id,r_creation_date from dm_folder where folder('/UN/Edu/test',descend) and DATEDIFF(day,r_creation_date,DATE(NOW)) <= (1.0/24)

For both you will get the same result only.

The Problem is with your query, see the below query of yours which is having a "/" at the end of the folder path used in descend which will always gives you "0"result.

smadduri wrote:HI Johnny,I tried your query select r_object_id from dm_folder where folder('/UN/Edu/test/',descend) and DATEDIFF(day,r_creation_date,DATE(NOW)) <= (1/24)But it does not return any records even i have folder  created today 1hr back and long back as well .I want to get folders that are created 1 hourr back from current date .

Hope this helps to avoid wrong assumption :-)

--Arun

manishanandswami

Hi Arun

Hope this helps to avoid wrong assumption :-)

select 1/2 from dm_server_config

will gives you 0(ZERO)

select 1.0/2 from dm_server_config

will gives you 0.5

above is still applicable if we use Sql Server as back End ....

oracle gives 0.5 for both queries .... airthmetic operation depends on DB level so better we use 1.0/2 which gives same result whatever the DB

DCTM_Guru

Thanks for detail clarification Manish

ArunSelvaraj

Thanks for the clarification Manish

--Arun