Datediff function of DQL is not returning results as expected

Question

I am trying to query documentum server using DQL query. Using DATEDIFF function to select data that are created in the current date. Here is the query

    SELECT title FROM content_table WHERE DATEDIFF(day, "r_creation_date", DATE(TODAY)) < '1' AND content_type IN ('story','news')

Problem is along with today's data its selecting yesterday's also. Why is less than 1 condition fetching yesterday's data also?

Have tried using DATEDIFF(day, "r_creation_date", DATE(TODAY)) = '0' but that does not fetch any result. I understand even the time comes into picture but as I am using 'day' as the date pattern will it not just calculate difference of the days alone?

http://stackoverflow.com/search?q=DATEDIFF+DQL – NitZRobotKoder Apr 12 '12 at 09:52 — NitZRobotKoder, Apr 12 '12 at 09:52

score 0 · Answer 1 · answered Apr 12 '12 at 09:58

0

I should use the GETDATE() function to get the current day

Regards.

answered Apr 12 '12 at 09:58

SELECT title FROM content_table WHERE DATEDIFF(day, "r_creation_date", GETDATE()) < '1' AND content_type IN ('story','news') This is what i used. But its throwing query pass error at GETDATE method. – NitZRobotKoder Apr 12 '12 at 10:24
@NitZRobotKoder is r_creation_date a column of content_table ? If so forget the quote and use content_table.r_creation_date – Apr 12 '12 at 10:38

score 0 · Accepted Answer · answered Apr 17 '12 at 10:15

0

You can try this query:

SELECT title FROM content_table WHERE r_creation_date > DATE(TODAY) AND content_type IN ('story','news')

if you need the objects created today (after 00:00, not in the last 24 h)

answered Apr 17 '12 at 10:15

Sorin

1,965
2
12
18

Datediff function of DQL is not returning results as expected

2 Answers2