crystal reports datepart and dateadd

I have a begindatetime field and a shift field I am passing as a parameter to a report.  If the shift =1, I want to return all records from the begindatetime up to midnight of that day.  
I was trying to do a datepart function to remove the time portion of this and then add it back, but I can't seem to get it right.
As well I have another piece of logic that says if the shift =2 then I want to return all records from the begindatetime up until the next day at 8:00 AM.  So in other words if the user passes in 4/18/2006 5:00 PM, I would want the range up to 4/19/2006 8:00 AM.  
Is there a better way to do this?
tegronakronAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wykabryanCommented:
select {shift}
case 1:
  datetime({begindatetime}) in datetime({currentdate}) to datetime({currentdate},ctime("11:59:59pm"))
case 2:
  datetime({begindatetime}) in datetime({currentdate}) to datetime(dateadd("d",1,{currentdate}),ctime("8:00am"))
default:
  datetime({begindatetime}) in datetime({currentdate}) to datetime({currentdate},ctime("11:59:59pm"))


something like this should help.
0
tegronakronAuthor Commented:
I tried this but it keeps giving me an error in this section
datetime({begindatetime}) saying that a date is required here.  But it is already a datetime field, so do I still need to convert to a datetime, or is that perhaps causing the error?
0
wykabryanCommented:
yes.. I was not sure if it was in datetime format or not.  CR will kick an error out if the column that you are apply such functions are already in that format. So remove the datetime and just leave the column name.

select {shift}
case 1:
  {begindatetime} in datetime({currentdate}) to datetime({currentdate},ctime("11:59:59pm"))
case 2:
  {begindatetime} in datetime({currentdate}) to datetime(dateadd("d",1,{currentdate}),ctime("8:00am"))
default:
  {begindatetime} in datetime({currentdate}) to datetime({currentdate},ctime("11:59:59pm"))


Now this also goes under the assumption you want to look at today.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

tegronakronAuthor Commented:
well it should only be from {begindatetime}  until {begindatetime}  at midnight.
0
wykabryanCommented:
What is the purpose of the Shift parameter?  I am probably missing and there is more to this report.  The reason I ask is because (in my head) all records potentially could go into both shifts.  Are you doing some grouping by Person or date?  It might change the way you code this formula.
0
tegronakronAuthor Commented:
Well there are 2 different shifts in the factory, and the shiftID is noted in the table with the history records, On first shift, I can get all the records by looking where the shiftID=1 and the date is 4/18/2006 up until midnight.    But it does not help in the following case for 2nd shift.  They will only want to look at let's say 2nd shift for 4/18/2006.  But the shift rolls over into midnight and continues into the next day.
So 2nd shift may last from 5:00 PM on 4/18/2006 until 2:00 AM on 4/19/2006 and I want only those records, this is why I need the date range for 2nd shift to be the next day until 8:00 AM.  They change the times of their shifts, but I always know they will be in a certain range.  I am grouping by line and then station but not by date.  
0
wykabryanCommented:
so do you have a parameter that will allow date input if they want to see a specific day?
0
tegronakronAuthor Commented:
yes, there is a parameter for shift and begindate.
So basically they would pass in shift 1 on 4/18/2006
or shift 2 on 4/18/2006 (implying that it would return all data from the whole 2nd shift i.e. data that goes into 4/19/2006)
0
wykabryanCommented:
Ok.. and then you are comparing the in between against the database.  


So you have shift and begindate as parameters and for this example a column name date

Go to Reports>Selection Formulas>Records
(
(if {shift} = 1 then {date} in {begindate} to datetime({begindate}, ctime("11:59PM"))) or
(if {shift} = 2 then {date} in {begindate} to datetime({begindate}, ctime("11:59PM"))
              and dateadd("d",1,{date}) to datetime(dateadd("d",1,date({begindate})),ctime("12:00am")) to
                                                        datetime(dateadd("d",1,date({begindate})),ctime("5:00am")))
)


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tegronakronAuthor Commented:
All right, that works great! Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.