Skip to Content
avatar image
Former Member

Calculate the number of days between two different dates in Crystal Reports

Hello,

I know how to calculate the number of days between two dates in SQL.

SELECT DATEDIFF(DAY, '2017-01-01' ,  '2017-01-31')

The query returns: 30

But I need to know how can implement that in Crystal Report.

Thanks for any help.
--Jean

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Feb 14, 2017 at 11:35 PM

    Hi Jean,

    Subtract the dates and that will give you the number of days like:

    Date (2017, 01, 31) - Date (2017-01-01); 

    This will return 30 like you want.

    Brian

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Jean,

      I'm confused. Because yyyy-mm isn't an actual date, you can't use date functions. If you subtract the two different "dates" like your example, what are you expecting the answer to be?

      Are your users selecting the dates from the calendar or are they just typing in 2017-01 and 2017-02?

      Brian

  • Feb 15, 2017 at 10:31 AM

    What data type are your start and end date parameters?

    Are they strings? If so you will need to convert to dates

    eg @Startdate

    Date(tonumber(left({?Startdate}, 4)), tonumber(right({?Startdate}, 2)), 1))

    Endate is more complex as you must first convert to first of following month and then determine last day of month. Also in this case you must use dateserial , if not it will fail in December as dateserial can accept 12+1, whereas Date will fail

    @Enddate

    Dateserial(tonumber(left({?Enddate}, 4)), tonumber(right({?Enddate}, 2))+1, 1)-1

    You can then use

    @Dateend - @StartDate

    or

    datediff('d', @StartDate, @EndDate)

    Ian

    Add comment
    10|10000 characters needed characters exceeded