Question
· 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

Discussion (4)2
Log in or sign up to continue