Date Functions in Documentum (DQL)

In OpenText Documentum, DQL date functions are used to manipulate, compare, and format date values stored in repository objects.

⚠️ Note: When using date functions in the SELECT list, you must use the AS clause to assign a column name.

1. DATEDIFF

The DATEDIFF function calculates the difference between two dates.

Syntax:

DATEDIFF(date_part, date1, date2)

• date_part → year, month, week, day
• Returns: difference (date2 − date1)

Example:

SELECT task_number, supervisor_name
FROM dm_tasks_queued
WHERE DATEDIFF(month, plan_start_date, actual_start_date) >= 1

👉 Tasks delayed by 1 month or more

SELECT task_number, r_task_user
FROM dm_tasks_queued
WHERE DATEDIFF(week, actual_start_date, DATE(TODAY)) >= 1

👉 Tasks started more than 1 week ago

DB Behavior:

• Oracle → floating value
• SQL Server → integer (except day = float)
• PostgreSQL → integer

2. DATEADD

The DATEADD function adds time to a date.

Syntax:

DATEADD(date_part, number, date)

Example:

SELECT task_number, supervisor_name
FROM dm_tasks_queued
WHERE DATEADD(week, 1, actual_start_date) < DATE(TODAY)
AND actual_completion_date IS NULLDATE
AND NOT(actual_start_date IS NULLDATE)

👉 Tasks started over a week ago and not completed

3. DATEFLOOR

The DATEFLOOR function rounds a date down to the beginning of a unit.

Syntax:

DATEFLOOR(date_part, date)

• year, month, day

Example:

SELECT DATEFLOOR(month, r_creation_date) AS created_month
FROM dm_document

👉 Converts:

• Mar 23, 1996 09:30 → Mar 1, 1996 00:00

4. DATETOSTRING

The DATETOSTRING function converts a date into a formatted string.

Syntax:

DATETOSTRING(date, 'format')

Example:

SELECT DATETOSTRING(r_creation_date, 'dd-mon-yy') AS date_val
FROM dm_document

👉 Output: 14-May-95

Common Formats:

• dd-mon-yy → 14-May-95
• mm/dd/yy → 05/14/95
• month yy → May 95
• yyyy_mm_dd hh_mi_ss

Important Notes

• Use AS when selecting function output
• Works on date-type attributes (e.g., r_creation_date)
• Supports DATE(TODAY) for current date
• Use NULLDATE to check for null date values

Summary

Conclusion

DQL date functions provide powerful tools for date calculations, filtering, and formatting, making it easier to build dynamic and time-based queries in Documentum.