Print Page | Close Window

DATE DIFF problem

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8084
Printed Date: 18 May 2024 at 1:37am


Topic: DATE DIFF problem
Posted By: NekNhok
Subject: DATE DIFF problem
Date Posted: 21 Oct 2009 at 1:11pm
I cannot get the simple number of days to work. I am trying to get the number of days between the day an order is entered and TODAY (the day the report is run). When I try to save the formula I get the error message that a date is needed where I have entered "query.date_entered"

The formula is something like: DATEDIFF("d",{query,date_entered},TODAY)

Anyone have any suggestions.........thanx, Ken

-------------
Ken


Replies:
Posted By: DBlank
Date Posted: 21 Oct 2009 at 1:26pm
check the field type for {query.date_entered}. If it is text you will need to convert it to a date type but that will be based on the string that is in it.
USually it is something like
Datediff('d', date({query.date_entered}),currentdate)

Posted By: NekNhok
Date Posted: 21 Oct 2009 at 6:37pm
The field type is not text.......it is defdinately a date maybe even date/time and maybe the time is what is causing the problem??? I'll try converting it to date only and see if that works.

-------------
Ken

Posted By: lockwelle
Date Posted: 22 Oct 2009 at 6:18am
what is TODAY?  CR uses currentDate as the function that returns the current date.

Posted By: DBlank
Date Posted: 22 Oct 2009 at 7:22am

Hey lockwelle, you can use 'today' to grab the current date and 'now' for current time although I almost always use currentdate, currenttime or currentdatetime.  Not sure why there are the variations but they do seem to function fine (at least in v10 and 11).

NeknHok, the datediff should function on a date or datetime.
when you place the {query.date_entered} field on the canvas and right click on it and select Formay Field there is definately a DATE or DATE AND TIME tab that appears for formatting?
 

Posted By: NekNhok
Date Posted: 22 Oct 2009 at 7:22am
Thanks for waking my sleeping brain.....the filed type was date & time(yyyy/mm/dd HH:MM:SS). I converted it to date (MM/DD/YYYY) and the formula works just fine. Thanks again

-------------
Ken

Posted By: NekNhok
Date Posted: 22 Oct 2009 at 7:24am
Thanks for the info....I'll remember it for the future. This time around I do not need the time and per my last posting I got it to work fine by converting the field. Thanks again........Ken

-------------
Ken

Posted By: DBlank
Date Posted: 22 Oct 2009 at 7:26am
Glad you got it.
I forget the variations on how the dates are stored and how that messes formula usage up.
It is a good reminder.

Posted By: lockwelle
Date Posted: 22 Oct 2009 at 7:43am
Thanks DBlank, I looked in Help, and it didn't reference Today or Now (I figured that Now should work as it the VB6 keyword, but since it wasn't in Help, I have been using CurrentDate).
 
As for the converting to a date, how interesting, one would think it work regardless (like it does in SQL).  I guess I don't use dateDiff in CR, but instead do it on the server, since I have not had issues (or all my dates are for 00:00:00, which is possible as well).
 
Thanks for enlightening me.


Print Page | Close Window