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

10|10000 characters needed characters exceeded

• Posted on 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

10|10000 characters needed characters exceeded
• Brian Dong Former Member

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

• Posted on 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