0

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

Feb 14, 2017 at 07:44 PM

144

Former Member

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

Brian Dong Feb 14, 2017 at 11:35 PM
0

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

Show 2 Share
Former Member

Hi Brian,
Sorry I need the dates in the different format. For example the desired format is yyyy-mm.

Let say there are two parameters (start date and end date) and the user enter to different dates like 2017-01 and 2017-02 which is the month of Jan. and Feb.

Thanks,
-- jean

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

Ian Waterman Feb 15, 2017 at 10:31 AM
0

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

Share