Date fields coming into Crystal as Date/Time 1

[GCL2007]

Hi - Wondering if anyone has ideas. I'm trying to create a report that reports Transaction records that I want to group into Fiscal Periods. I have a Periods table with Start and End Dates for the Period. I am just trying to report transactions by Period (for example, March 29 to April 25). In my Database Expert I linked from my Transactions table to the Periods table on Trans Date with a >= inner join link to Period_Start_date and a <= link to the Period_end_date fields. My issue is that I missing any transactions on that last day of the period If I print that Period_End_date field in the report I am seeing 4/25/2021 12:00:00 AM, while in my database I am just seeing 4/25/21. On my report I believe I am missing all transactions done on 4/25. Any one have thoughts on how to get around this?
Thanks!

 

[kray4660]

I normally use a command. So in MS-SQL there are two possible ways for the upper bound. SomeField < AnotherField + 1 (effectively adds one to the date) or SomeField < DATEADD(d,1,AnotherField).

Hope this makes sense.

 

[GCL2007]

Thanks - I ended up creating a command table and used the SomeField < AnotherField + 1 approach. I'm happy to see you agreed with what I did... Seems to work fine.. Thanks!