Calculated Fields - Subtract Dates

Paul shared this question 4 years ago
Answered

How can I subtract dates in a Simple calculation? I need to use When/Else statements so I don't want to use the Pre-defined calcs.

FYI, I don't actually know the version.

Thanks.

Replies (10)

photo
1

Hi Paul,

Thanks for reaching out. Can you explain your actual use case for this? You can't subtract, for example, days, from a date field because you cannot perform arithmetic expressions between DATE/DATETIME and NUMERIC fields. To accomplish something along this lines will likely require drafting a Freehand SQL statement utilizing one of the RDBMS-specific date functions, e.g., DATEDIFF in Microsoft SQL Server, but if you can let me know what you're actually trying to do I can at least provide some pointers to get you headed in the right direction.

Regards,

Mike


Regards,

Mike

photo
1

Hi Mike,

Thanks for replying so quickly. I have a Created Date for each record and either a Closed Date #1 or a Closed Date #2 for each record. I am trying to calculate the number of days in between the Created Date and whichever Closed Date that is present in the record. I was hoping I could calculate - if Closed Date #1 is null, then Closed Date #2 minus Created Date, else Closed Date #1 minus Created Date. Hope that makes sense. Thanks!

photo
1

Hi Paul,

If you create a Calculated Field with a Freehand SQL statement along these lines you should be good to go:

CASE WHEN dateint_date IS NULL then datediff(dateint_date2,CreateDate) ELSE datediff(dateint_date,CreateDate) END
You'll have to of course replace with your own details, and may have to alter some of the syntax depending on your RDBMS, but this would be the template to accomplish this. Please let me know how goes and whether you have any further questions.

Regards,

Mike

photo
1

Hi Paul,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
photo
1

Hi Mike,

To preface, I am not a SQL expert, but I do have expert support here that I have used.

When we use the statement you suggested, I receive the following error:

SQL statement is invalid.

The error message returned from the database was:

ERROR: function datediff(date, timestamp with time zone) does not exist

Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Position: 9

Any help would be appreciated. Thanks!

Paul Janzen

Sr. Business Consultant – Investigation Services

Manulife

E paul_j_janzen@manulife.ca

T 519 747 7000 x242825

25 Water St. South

Kitchener, ON, N2G 4Z4

b20c8ebb5de94324ea8f86d75be4209c

 

Benefits fraud hurts us all. Be part of the solution. manulife.ca/shareandprotect

 

Without standards, there can be no improvement.     Taiichi Ohno

From: Support Queue <support@Yellowfin.bi>

Sent: Thursday, October 24, 2019 6:13 PM

To: Paul J. Janzen <Paul_J_Janzen@manulife.ca>

Subject: New Comment in "Calculated Fields - Subtract Dates"

photo
1

Hi Paul,

Thanks for your response. This was an example using Microsoft SQL Server. If your data source's RDBMS is different, which based on the "function... does not exist" error, this is almost certainly the case, this will require altering the syntax and/or a differently named function. You should be able to find this information under Application Details in your info.jsp file, which can be accessed by appending '/info.jsp' to your login URL. For example, localhost:8080/info.jsp. If you can provide this file to me I can see the db you're using (assuming you're not using a different db for data sources vs. what you're using for the configuration database), I can try and alter the statement for your particular case.

Regards,

Mike

photo
1

Hi Paul,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

Hi Mike,

Unfortunately, I tried your suggestion and received the following error:

4bbfe9114a3573689d2c02b3eaa7a3d3

Paul Janzen

Sr. Business Consultant – Investigation Services

Manulife

Epaul_j_janzen@manulife.ca

T 519 747 7000 x242825

25 Water St. South

Kitchener, ON, N2G 4Z4

bab1dd8d1afb328029590ed7ff6fbc6d

 

Benefits fraud hurts us all. Be part of the solution. manulife.ca/shareandprotect

 

Without standards, there can be no improvement.     Taiichi Ohno

From: Support Queue <support@Yellowfin.bi>

Sent: Tuesday, November 5, 2019 1:24 PM

To: Paul J. Janzen <Paul_J_Janzen@manulife.ca>

Subject: New Comment in "Calculated Fields - Subtract Dates"

photo
1

Hi Paul,

Thanks for your response. If the file were accessible, I'd think it'd be manulife.i-sight.com/info.jsp not manulife.i-sight.com/login/info.jsp, but there's a good chance the file's inaccessible regardless.

There's another way to find this information, however. You can find these details from within the application under Administration > System Information > Application Details.

Regards,

Mike

photo
1

Hi Mike,

Under Administration, I only have access to User Groups and Content Folders.

I am starting to think that I need to contact i-Sight – the vendor that we were given Yellowfin from.

If you don’t have any other suggestions, then that is what I am going to do.

Paul Janzen

Sr. Business Consultant – Investigation Services

Manulife

Epaul_j_janzen@manulife.ca

T 519 747 7000 x242825

25 Water St. South

Kitchener, ON, N2G 4Z4

7b816ec750c520cf1af1aed08e5a4217

 

Benefits fraud hurts us all. Be part of the solution. manulife.ca/shareandprotect

 

Without standards, there can be no improvement.     Taiichi Ohno

From: Support Queue <support@Yellowfin.bi>

Sent: Wednesday, November 6, 2019 11:48 AM

To: Paul J. Janzen <Paul_J_Janzen@manulife.ca>

Subject: New Comment in "Calculated Fields - Subtract Dates"

photo
1

Hi Paul,

The ability to see the specified section does indeed depend on permissions assigned to your role. You'd also be able to find this information in your Data Source's Connection Settings, but if you're only seeing User Groups and Content Folders under Administration that won't be an option either. As such, if no one internally knows what db your data source is built on and you don't have the permissions to find this information out, then yes, I'd assume I-Sight would be able to inform you of what RDBMS is being used. At which point you can then alter the query provided earlier to the syntax specifications of the database type that's being used.

Regards,

Mike

photo
1

Hi Paul,

I just wanted to check in and see how things are going with this. Do you need anything else from my end here?

Regards,

Mike

photo
1

Hi Paul,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you and I'm afraid Support can't assist much further besides, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to assist further.

Regards,

Mike

Leave a Comment
 
Attach a file