Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

I have two dates of the structure Start Date: 2007-03-24, End Date: 2009-06-26

Presently I want to track down the difference between these two in the beneath structure:

2 years, 90 days, and 2 days

I Have gone through the procedure described here but I'm a Little bit scared if I tried wrong. Can someone give some insight here ?

asked 24 Sep '22, 08:17

sarthakjain's gravatar image

accept rate: 0%


I suggest you post what you've tried so far to get some feedback.

(24 Sep '22, 12:41) Justin Willey

Try this:

create or replace function DateDifference(in in_StartDate date,in in_EndDate date)
returns varchar(64)
 declare l_Years integer;
 declare l_Months integer;
 declare l_Days integer;
 declare l_Date date;

 select datediff(year,in_StartDate,in_Enddate) into l_Years;

 set l_Date = dateAdd(year,l_years,in_startDate);
 select datediff(month,l_Date,in_EndDate) into l_Months;

 set l_Date = dateAdd(month,l_months,l_Date);
 select datediff(day,l_Date,in_EndDate) into l_Days

 return (if l_Years > 0 then l_Years ||' years ' endif) ||
        (if l_Months > 0 then l_Months ||' months' endif) ||
        (if l_Days > 0 then ' and '|| l_Days ||' days' endif) 

select DateDifference('2007-03-24','2009-06-26') will return 2 years, 3 months and 2 days

permanent link

answered 26 Sep '22, 03:04

Frank%20Vestjens's gravatar image

Frank Vestjens
accept rate: 20%


Note that this will not work correctly for all dates.

If the second date has a lower value for either MONTH or DAY, it will ignore the MONTH and/or DAY. DateDifference('2022-11-30','2022-12-01') // returns 1 month DateDifference('2022-12-31','2023-01-01') // returns 1 year

Both of these should be 1 day.

(28 Sep '22, 18:25) Chris Keating
Replies hidden

You're right. It might need some adjustments to get it working properly

(29 Sep '22, 02:10) Frank Vestjens

It would probably be easy to swap dates internally if StartDate is before EndDate and "negate" the result then.

(29 Sep '22, 04:18) Volker Barth

It's hard to say what eyactly you need, have you tried casting the strings to DATE and using the DAYS function of Anywhere?

permanent link

answered 25 Sep '22, 11:00

tedfroehlich's gravatar image

accept rate: 18%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 24 Sep '22, 08:17

question was seen: 1,047 times

last updated: 04 Apr, 07:20