Compass Query in Data Fabric for Date

Hello - I'm trying to write a query using Compass that will return the first day of the previous month. For example, today is 2024-02-22, so I want the query to return the date of 2024-01-01.

Anybody know how to do that? It needs to be able to account for a different number of days in a month and if it is January then the previous year is minus 1 as well.

Find more posts tagged with

Infor OS Portal

Accepted answers

andy

Have you taken a look at the datetime functions available in the Compass SQL reference? You probably can accomplish that by using a combination of some of those functions. For example...

SELECT DATE_SUB(LAST_DAY(ADD_MONTHS(CURRENT_DATE(),-2)),-1)

I'd take a look at the functions though. There's might be a more efficient way than this; just wanted to illustrate some of the functions

https://docs.infor.com/inforos/2023.x/en-us/useradminlib_cloud/default.html?helpcontent=datafabrug/dzi1631199611238.html

All comments

andy

Have you taken a look at the datetime functions available in the Compass SQL reference? You probably can accomplish that by using a combination of some of those functions. For example...

SELECT DATE_SUB(LAST_DAY(ADD_MONTHS(CURRENT_DATE(),-2)),-1)

I'd take a look at the functions though. There's might be a more efficient way than this; just wanted to illustrate some of the functions

https://docs.infor.com/inforos/2023.x/en-us/useradminlib_cloud/default.html?helpcontent=datafabrug/dzi1631199611238.html

unknown

@patrick-webley - FYI, you can make use of available datetime functions and achieve the use case that you'd asked for. Even, the solution suggested by @andrew-tarver works!!!

Stace Webley

Thank you both for the response. I had spent time digging into those functions and more time wasted than I'd like to admit trying to figure it out. I just needed help from someone smarter than me. Thank you!

Stace Webley

Thank you, Andy!

Copyright © 2025 Infor. All rights reserved.