Written by

IRIS Developer Advocate, Software developer at CaretDev, Tabcorp
MOD
Question Dmitry Maslennikov · Mar 29, 2023

DATE_TRUNC with IRIS SQL

Most of the databases with SQL access offers the DATE_TRUNC function or analogs. This helps to easily truncate a date by any part while keeping the DateTime in a valid format

I found it quite tricky to implement the same function with IRIS, some intervals can be done quite easily, when such intervals as quarter, week, decade and so on I did not find how to implement, yet

SELECT
 CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-01-01 00:00:00' ) AS DATETIME ) "year",
 CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-MM-01 00:00:00' ) AS DATETIME ) "month",
 CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-MM-DD 00:00:00' ) AS DATETIME ) "day",
 CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-MM-DD HH:00:00' ) AS DATETIME ) "hour",
 CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-MM-DD HH:DD:00' ) AS DATETIME ) "minute",
 CAST ( TO_CHAR ( CAST ( some_date AS DATETIME ) , 'YYYY-MM-DD HH:DD:SS' ) AS DATETIME ) "second",
 CAST ( some_date AS DATETIME ) "original"
FROM ( SELECT '2023-03-28 14:48:12.183590' AS some_date )

Any ideas on how to implement the missing intervals?

And I expecting SQL only ways, I know I can write some SQL Procedure, but it's not the case

Comments

Benjamin De Boe · Mar 30, 2023

Hi Dmitry, we've recently been working on this function and a bunch of SQL optimizer enhancements to leverage it in query processing. I can't promise an exact release date, but it definitely will be this calendar year.

0
Dmitry Maslennikov  Apr 3, 2023 to Guillaume Rongier

It's a bit more complicated for this task.

It always should return the full datetime, for instance if I trunc to quarter, it should return 2023-04-01 for the current day

0