| Author |
Message |
namitanamburi
Newbie
Joined: 26 Mar 2009
Online Status: Offline
Posts: 39
|
  
Topic: Date &Timestamp Issue
Posted: 13 May 2009 at 7:29am |
|
Hi,
Is there anyway I can run a report and make sure that all the date fields retrieved are going to be searched for dates and not time stamps, the record retrieval should truncate the minutes and seconds and search for just the date range.
This is the portion of the query.the date ranges are between 8/2 and 8/3. Can you avoid the query searching for 00:00:00 and instead just the dates.
("table"."field">={ts '2007-08-02 00:00:00'} AND ("table"."field")<{ts '2007-08-03 00:00:00'})
Thanks
Namita
Edited by namitanamburi - 13 May 2009 at 7:29am
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 May 2009 at 7:42am |
you can convert your datetime to a date type:
date({table.field}) in #08/02/2007# to #08/03/2007#
|
| IP Logged |
|
namitanamburi
Newbie
Joined: 26 Mar 2009
Online Status: Offline
Posts: 39
|
Posted: 13 May 2009 at 9:07am |
|
Hi,
where do I write this formula, also just fyi the datatype of this fiels id date in database but just when crystal generates sql query it puts the ranges as ts< and ts>,,,,not sure why :(
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 May 2009 at 9:10am |
I assumed you were using this to select data so it goes in the select statement in the select expert to filter your data to only pull records that are on 8-2 or 8-3.
|
| IP Logged |
|
namitanamburi
Newbie
Joined: 26 Mar 2009
Online Status: Offline
Posts: 39
|
Posted: 13 May 2009 at 9:14am |
|
Hi,
I am using something like
{table.field} is in {?start_date} to {?End_date}.
When prompted I entered 8/2 and 8/3.
The field in table is of datatype date and also I entered parameter datatypes as date
|
| IP Logged |
|
namitanamburi
Newbie
Joined: 26 Mar 2009
Online Status: Offline
Posts: 39
|
Posted: 13 May 2009 at 9:20am |
|
As you mentioned, I inserted this in selection expert
date({table.field}) in {?Start_Date} to {?End_Date}
And I get a prompt
A date-time is required here.
The datatype for everything is date yet it wants date -time.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 May 2009 at 9:27am |
What did it highlight?
what is the table.field name you used?
|
| IP Logged |
|
namitanamburi
Newbie
Joined: 26 Mar 2009
Online Status: Offline
Posts: 39
|
Posted: 13 May 2009 at 10:06am |
|
date({HISTORY.TRANDATETIME}) in {?Start_Date} to {?Start_Date}
This is the statement, It gives error message
A date-time is required here.
The cursor highlights beside date({HISTORY.TRANDATETIME})I
Just the place where I highlighted with a red I.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 May 2009 at 12:23pm |
Odd....if all 3 are supposedly date type fields try:
{HISTORY.TRANDATETIME} in {?Start_Date} to {?Start_Date}
If you are still getting a datetime is required something else is going on...
Are your parameters fields crystal parameters built in the crystal interface or something else?
|
| IP Logged |
|
namitanamburi
Newbie
Joined: 26 Mar 2009
Online Status: Offline
Posts: 39
|
Posted: 13 May 2009 at 1:15pm |
|
Unfortunately all of them are of datatype dates and the parameters are Crystal parameters.
This is the only report when run on a Joboss server searches date ranges as d and not as ts as it does for other reports.
Can you please respond to my post Subreports that I posted today. It is really very urgent.
Thanks
Namita
|
| IP Logged |
|
|
|
