• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8232
  • Last Modified:

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?
0
tegronakron
Asked:
tegronakron
  • 5
  • 5
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
tegronakronAuthor Commented:
All right, that works great! Thanks
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now