Compass query date = today

Nathalie Abel

I want to query Dala Lake. I want to retreive all invoices entered today. How can I do that ? enterdt = ?

Find more posts tagged with

Infor Data Fabric

Accepted answers

Kevin Heiman

All MS SQL dialect and some Parquet dialect can be used.

You need to format either the Column date or the GETDATE to match you needs. Here is a simple example.

select
substring(to_iso8601(infor.lastmodified()), 1, 10) as "Last Mod Date"
, LMDT
, LMTS
, PPDT
, RGDT
, convert(varchar, getdate(), 112) as "current Date"
from infor.allvariations('MITMAS')
where RGDT < convert(varchar, getdate(), 112)

LMDate	LMDT	LMTS	RGDT	current Date
2020-07-23	20,200,723	1,595,502,939,003	20,200,710	20220118
2020-07-23	20,200,723	1,595,509,162,459	20,200,529	20220118
2020-07-23	20,200,723	1,595,525,108,521	20,200,529	20220118
2020-07-23	20,200,723	1,595,525,342,397	20,200,716	20220118
2020-07-23	20,200,723	1,595,525,353,606	20,200,716	20220118

All comments

unknown

Check if this works for your requirement. you can use the below fields for the date.

RGDT -> Record created date

LMDT -> Record Last modified date

select * from OINVOH where RGDT = '20220117'
(or)
select * from OINVOH where LMDT = '20220117'

Nathalie Abel

Sudhakaran, I don't want to put a date because this query will run everyday. That is why I need something that means today

unknown

Hi Nathalie, you should be able to do this using Current_timestamp and DatePart. See the ION Development Guide (Cloud Edition) for details on that functionality in compass.

Kevin Heiman

All MS SQL dialect and some Parquet dialect can be used.

You need to format either the Column date or the GETDATE to match you needs. Here is a simple example.

LMDate	LMDT	LMTS	RGDT	current Date
2020-07-23	20,200,723	1,595,502,939,003	20,200,710	20220118
2020-07-23	20,200,723	1,595,509,162,459	20,200,529	20220118
2020-07-23	20,200,723	1,595,525,108,521	20,200,529	20220118
2020-07-23	20,200,723	1,595,525,342,397	20,200,716	20220118
2020-07-23	20,200,723	1,595,525,353,606	20,200,716	20220118

Nathalie Abel

Kevin, I don't get it. here is my query:

select orderno, ordersuf, paiddt from oeeh where paiddt = '2022-01-18' (want to replace the date by today)

unknown

Hi

I tested Kevin's query and it worked well in the compass. The below query showed records that are updated today.

select * from OINVOH where RGDT = convert(varchar, getdate(), 112)

*convert(varchar, getdate(), 112)* gives the date in format 20220118 it seems

docs.microsoft.com/.../cast-and-convert-transact-sql

Kevin Heiman

Correct, the two date formats need to match for the comparison. Either change the column date to ISO8601 so it matches the getdate default... or change the getdate to match the column date.

The guidance by @rpow987 is also an option , using current_timestmap. It woudl need be converted to date format to match.

I tried to include a few variations as shown in the result table after the query.

LMDT and RGDT as their default value of integers.
LMDate as converted ISO8601 date
current Date as getdate converted to format 112.
Also the LMTS which is an EPOC time with milliseconds. It takes other conversions to format that.

antonio-caria

RGDT and LMDT are numeric so You should not use ' between values.

You can Use also:

select * from OINVOH where RGDT = 20220117 or LMDT = 20220117

For large tables that should be avoided..

Copyright © 2025 Infor. All rights reserved.