Record selection for date time problem. 1

[urbanhim]

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

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[PatriciaObreja]

For today:

DateTime(CurrentDate & Time("17:00"))

For tommorow:

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

 

[urbanhim]

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

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[PatriciaObreja]

{@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

 

[urbanhim]

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

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[CoSpringsGuy]

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

 

[urbanhim]

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

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[urbanhim]

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

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[PatriciaObreja]

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

 

[urbanhim]

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

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[CoSpringsGuy]

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

 

[PatriciaObreja]

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

 

[CoSpringsGuy]

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

 

[CoSpringsGuy]

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

 

[lbass]

Try:

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

-LB

 

[urbanhim]

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

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]