Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Record selection for date time problem. 1

Status
Not open for further replies.

urbanhim

MIS
Jul 11, 2007
154
GB
I have two fields, one for Date and one for Time, I have joined them in a formula field as follows:

@DateTime
datetime(date({Detail.DateChangeDue}),time({Detail.TimeChangeDue}))

This is great, its showing 28-Feb-2008 17:00

This field shows previous and future dates and time. This report is to be run daily, but should only show records where the date is between Today 17:00 and tomorrow at 17:00

I really dont know how to do this, assume its something like "CurrentDate 17:00:00 in CurrentDate+1 17:00:00" but I have no idea.

Many thanks



UrbanHim
Crystal XI Report Writer
London
[shadeshappy]
[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]
 
For today:
Code:
DateTime(CurrentDate & Time("17:00"))
For tommorow:
Code:
DateTime(CurrentDate & Time("17:00")) + 1

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium
 
Thanks for your post, I have created a record selection formula as follows:

{@DateTime} in (CurrentDate & Time("17:00")) to {@DateTime}(CurrentDate & Time("17:00")) + 1

But its not accepting unfortunately! I must be doing something wrong!!

Thanks

UrbanHim
Crystal XI Report Writer
London
[shadeshappy]
[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]
 
Code:
{@DateTime} in 
(DateTime(CurrentDate & Time("17:00")) to (DateTime((CurrentDate & Time("17:00"))) + 1))

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium
 
I'm now getting "Bad Date Time Format String" any ideas why this might be?

The fields were originally string, but I have converted them to date and time respectively before joining them in my @DateTime formula.

Thanks

UrbanHim
Crystal XI Report Writer
London
[shadeshappy]
[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]
 
urban,

I think your issue is the way you chose to name your variable. (datetime) make sure when you use PAtricias suggestion you use DateTime( and not {@DateTime}(

I would also suggest changin the formula slightly to

{@DateTime} in
(DateTime(CurrentDate & Time("17:00")) to (DateTime((CurrentDate & Time("16:59:59"))) + 1))

else there is a slight possibility of an overlapping record.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
Its still saying "bad date time string" even though my selection criteria is simply this:

{@DateTime} in
(DateTime(CurrentDate & Time("17:00:00")) to (DateTime((CurrentDate & Time("16:59:59"))) + 1))


UrbanHim
Crystal XI Report Writer
London
[shadeshappy]
[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]
 
This works when I physically choose a date:

{@DateTime} in DateTime (2008, 02, 28, 17, 00, 00) to DateTime (2008, 02, 29, 17, 00, 00)

UrbanHim
Crystal XI Report Writer
London
[shadeshappy]
[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]
 
The problem might be with:
DateTime(date({Detail.DateChangeDue}),time({Detail.TimeChangeDue}))
I think that we have to see some sample data.


-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium
 
OK, here's whats going on from scratch and sample format

Details.DateChangeDue(String)
2007-02-28

Detail.TimeChangeDue(String)
17:00:00

I have created a three new formula fields to convert these to DateTime, I probably could've done this all in one, but didnt know how, so it took three formulas...

@DetailDate
Date(val({Detail.DateChangeDue}[1 to 4]),val({Detail.DateChangeDue}[6 to 7]),val({Detail.DateChangeDue}[9 to 10]))

@DetailTime
Time (val({Detail.TimeChangeDue}[1 to 2]),val({Detail.TimeChangeDue}[4 to 5]),val({Detail.TimeChangeDue}[7 to 8]))

Then joined it into one field using this formula:

@DateTime
datetime({@Date},({@Time}))

And this is where we are at now, hopefully this will prove a little more easier to crack now!

Thank you

UrbanHim
Crystal XI Report Writer
London
[shadeshappy]
[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]
 
hmmmm try ...

{@DateTime} in
(DateTime(CurrentDate & Time(17,00,00)) to (DateTime((CurrentDate & Time(16,59,59))) + 1))


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
For these values your formulas work fine, but I think that in the database are some values for which returns this error.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium
 
looking at the stucture of your DB text field it seems this would work for your @datetime formula:

IF ISDATE("2007-02-28") THEN
IF ISTIME("17:00:00") THEN
DATETIMEVALUE(DATE("2007-02-28"),TIME("17:00:00"))

replace your db fields of course and it would look like this:

IF ISDATE({Detail.DateChangeDue}) THEN
IF ISTIME("17:00:00") THEN
DATETIMEVALUE(DATE({Detail.TimeChangeDue}),TIME("17:00:00"))

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
whups ............

IF ISDATE({Detail.DateChangeDue}) THEN
IF ISTIME({Detail.TimeChangeDue}) THEN
DATETIMEVALUE(DATE({Detail.DateChangeDue}),TIME({Detail.TimeChangeDue}))

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 
Try:

{@DateTime} in DateTime(CurrentDate,Time(17,00,00)) to DateTime(CurrentDate+1,Time(16,59,59))

-LB
 
Thanks to you all for your help and formula suggestions, its much appeciated.

LB, your formula is spot on and works a treat. Briliant, thanks!

UrbanHim
Crystal XI Report Writer
London
[shadeshappy]
[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top