FileNet - SQL for a date property between specific hours

Question

I would like to fetch documents that a property date hour is between midnight and 4 AM. I tried this:

SELECT [This], [Date], FROM Folder_Type_1
WHERE DATEPART(hh,[Date]) >= 0
AND DATEPART(hh,[Date]) <= 4
ORDER BY Date

and

SELECT [This], [Date], FROM Folder_Type_1
WHERE CONVERT(VARCHAR(8),Date,108) between '00:00:00' and '04:00:00'
ORDER BY Date

But none of them is working when I test it in the SQL query builder in the FEM. DATEPART and CONVERT are not recognised. What is the correct way to do it?

I didn't find anything interesting in this SQL syntax reference.

Thank you in advance!

Answer 1

I have used the follwoing before:

where c.DateCreated >= 20130101T000000Z

This is a snippet from a query executed using the api an not the fem, but in principle this should be the same sql

Answer 2

You are trying to use T-SQL functions within Content Engine Query Language. While its syntax might look like SQL, it is actually not. Not to mention it is obviously not T-SQL.

As of today, it is not possible to accomplish what you want. TimeSpan function introduced in the version 5.1 allows some manipulations with date parts. Those, however, are not sufficient for your task. You might want to check TimeSpan documentation.