CONVERT DATETIME FIELD TO DATE ONLY

hello @koboldinst ,

I know Date formatting is tricky & I know I’ve seen similar questions asked before.  Did you notice this post answered by Gabriel Michaud?  Please keep reading beyond the “Best Answer” post because I think Brian McMillan posted the answer to your question above (removing time from a Date field to acheive a condition that recognizes a match and returns records with a certain filtered date), further down the thread.

I hope this helps you!

Laura

Hi,

You can use following parameters of Format function:

format('{0:MM/dd/yyyy}', [StartDate]) 

@Laura02 Thanks, I had already found that answer and tried it.  If i put it into the Data field, it still returns the time.

@Yulias18 That did not work putting it into the Data field

Thanks though!

@koboldinst Can you please try this formula?

=Concat(CStr(Month([CRActivity.StartDate])),'/' , CStr(Day(  [CRActivity.StartDate])),'/',CStr(Year(  [CRActivity.StartDate])))

Hi Naveen - Yes that works, I had already tried that but i need it to show just for today...and that GI data is then being brought into a master Velixo worksheet.  So i need it to filter  =today() somehow and that’s the part that’s not working.  I can filter on the results screen but that won’t help me use the data for a Velixo report.

Use this to get a datetime field to just date so it can compare it to today() or any other smalldate field

=DateAdd( [POReceipt.CreatedDateTime],'d',0)

This expression formats the date with two-digit day and month (dd/MM/yyyy):

=Concat(
    PadLeft(Day([Table.Date]), 2, '0'),
    '/',
    PadLeft(Month([Table.Date]), 2, '0'),
    '/',
    Year([Table.Date]))

Cheers,

You can use the same datetime field but choose a schema that you know displays the date only. For example, if the field formula you use is “Today()”, it will show in date time 12/10/2025 12:00AM. However, if you add the schema field of ARTran.Date, the field will show only 12/10/2025. 

Hope this helps!

Dom

This is very frustrating Acumatica doesn’t have a simple formula function to do this frankly. Since they don’t give us direct SQL access to write queries yet at least, we are left to deal with GI and they are just not robust or powerful enough to handle anything beyond a couple joins. The aggregate functions don’t work well either.