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

[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

[Former Member]

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

[former_member292966]

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

[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_member292966]

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