cancel
Showing results for 
Search instead for 
Did you mean: 

linking on date/time field

Former Member
0 Kudos

In a medical office I need to link two files 1) Appointments  2) Charges.  I am linking on Account# and Date.  The problem is that in the Appointment file, the field is a date/time (being the date and time of the appointment).  In the Charges file, the field is a date/time where the time is always 12:00:00.  The link, of course, is not working.  My question is: Is there a way to link on only the date and ignore the time?

Accepted Solutions (0)

Answers (2)

Answers (2)

abhilash_kumar
Active Contributor
0 Kudos

Hi Beckie,

If you can create a sql query with all the fields you need, that would be the best thing to do.

In the Join syntax you would need to convert the datetime fields to date so that the time part is ignored.

Another way to do this is to use a Linked Subreport along with a formula in the main report that modifies the time portion to 12:00:00. Of course, this method would have performance issues.

-Abhilash

0 Kudos

And be aware, if you create formula or alter the format CR will not be able to add the date field to the SQL Query and thus all rows will come down to the local PC and Date filtering will be applied on the second pass.

You may see a performance issue...

Don

0 Kudos

Hi Beckie,

Moved your post to the Report Design forum.

Data/time field types depends a lot on the DB and how you have the field types filtered/set in CR Designer.

So we need to know what version of CR Designer you are using and what and how you are connecting to the database?

Don