| Author |
Message |
NekNhok
Newbie
Joined: 21 Oct 2009
Location: United States
Online Status: Offline
Posts: 4
|
   Topic: DATE DIFF problem
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
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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)
|
|
IP Logged |
|
NekNhok
Newbie
Joined: 21 Oct 2009
Location: United States
Online Status: Offline
Posts: 4
|
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
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 22 Oct 2009 at 6:18am |
|
what is TODAY? CR uses currentDate as the function that returns the current date.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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?
Edited by DBlank - 22 Oct 2009 at 7:23am
|
|
IP Logged |
|
NekNhok
Newbie
Joined: 21 Oct 2009
Location: United States
Online Status: Offline
Posts: 4
|
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
|
|
IP Logged |
|
NekNhok
Newbie
Joined: 21 Oct 2009
Location: United States
Online Status: Offline
Posts: 4
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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.
|
|
IP Logged |
|
|
|
