Time Offset for When User Picks Date Range

Question

Currently my program allows users to enter in their date range, 1st or 2nd shift, then a fixed one hour time increment (1:00-2:00, 3:00-4:00, etc...). The problem I have is that 2nd shift, runs until 3:00 AM. So if the User wants second shift date from 2/20 - 2/21, they won't get any data from 12:00 - 3:00 AM because technically that is the morning of 2/22. How do I change it so when the user picks a date, the start of that date is 5:00 AM instead of 0:00.

I am using SQL Server 2008 R2 Report Builder to creat my report.

Answer 1

We don't see your code, or what it is written in, so it is difficult to help. Since this a T-SQL forum, I supposed you have some SQL somewhere, but what does it look like? Can you be more specific?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Hi jpollon

http://blog.sqlauthority.com/2010/07/16/sql-server-datetime-function-todatetimeoffset-example/

---

Pérez

Answer 3

Hi Jpollon,

You can append year and time to that date part and then transfer it to be a datetime like

select cast('2/22'+'/2013 5:00 AM' as datetime)
go

---

Many Thanks & Best Regards, Hua Min

Answer 4

 AND Testerline1_CycleTimes.StationEntry Between @Start And @End
  AND Testerline1_CycleTimes.StationEntry >= @Start
  AND Testerline1_CycleTimes.StationEntry < @End

Answer 5

Try

...

 AND Testerline1_CycleTimes.StationEntry Between cast(@Start+'/2013 5:00 AM' as datetime) And cast(@End+'/2013 5:00 AM' as datetime)

---

Many Thanks & Best Regards, Hua Min

Answer 6

I get this error when I add

AND Testerline1_CycleTimes.StationEntry Between cast(@Start+'/2013 5:00 AM' as datetime) And cast(@End+'/2013 5:00 AM' as datetime)

'The conversion of char data type to a datetime data type resulted in an out of range value.

Answer 7

Given the minimum of information you share, you should not be surprised that out feebles guess fail you.

What is the data type of Testerline1_CycleTimes.StationEntry? What is the data type of @Start and @End?

Besides the condition you posted:

  AND Testerline1_CycleTimes.StationEntry Between @Start And @End
  AND Testerline1_CycleTimes.StationEntry >= @Start
  AND Testerline1_CycleTimes.StationEntry < @End

appears to be redudant.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

My information is limited because I am very new to this and do not know exactly what is important info and what is not so I apologize.

I set @Start and @End as in the parameters section as Date/Time. When they are selected calenders appear for the user to pick a date. I am not sure what Testerline1_CycleTimes.StationEntry date type is but it represents a cell with a Date and a time in the format MM/DD/YYYY HH/MM/SS AM/PM.

For my redundant code which do you recommend keeping? Is there an advantage to having the between statement as opposed to the two greater/less than statements?

Answer 9

I'm not sure what you mean with "parameter section". It must be possible to post more code than you have done so far. Is that a stored procedure you have, or something which is embeeded into C# or similar.

To find out what data type Testerline1_CycleTimes.StationEntry has, you can use "sp_help Testerline1_CycleTimes", or right-click the table in SSMS and select Script as Create.

Which you should keep of the redudant option, depends the business logic. They are not equivalent. Between implies <= @End.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

I get this error when I add

AND Testerline1_CycleTimes.StationEntry Between cast(@Start+'/2013 5:00 AM' as datetime) And cast(@End+'/2013 5:00 AM' as datetime)

'The conversion of char data type to a datetime data type resulted in an out of range value.

Hi,

What are the values inside both @start and @end?

---

Many Thanks & Best Regards, Hua Min

Answer 11

Thanks for being patient with me. I have 4 parameters and 3 datasets. DataSet3 and DataSet1 represent my two assembly lines. They pull data from the SQL Server. DataSet2 just has the names of all of the stations from each assembly line. For my @Start and @End parameters, I do not have any values specified.

AND Testerline1_CycleTimes.StationEntry Between @Start And @End

This line of code is in the query of dataset3 and dataset 1. This connects my parameters with my queries. I have it so the user picks the dates for @Start and @End from calender pickers. To add the parameters I right-clicked on parameters section, chose add parameter, then made it type Date/Time. I did not have to code them into the program.

To create this I used a mixture of coding and the computer-aided wizards SSRS provides to help you.

Answer 12

Now try

AND Testerline1_CycleTimes.StationEntry Between cast(convert(varchar,@start,112)+' 5:00 am' as datetime) and cast(convert(varchar,@end,112)+' 5:00 am' as datetime)

---

Many Thanks & Best Regards, Hua Min

Answer 13

It did not get an error but it did not change my results. Here is my query after I added your code.

DECLARE @StartHour INT
DECLARE @EndHour INT

SET @StartHour = CASE @TimeRange

WHEN 0 THEN 6 --Start of Overall--
WHEN 1 THEN 6 --1st Shif Start--
WHEN 2 THEN 16 --2nd Shift Start--
WHEN 3 THEN 6 --6:00 AM Start--
WHEN 4 THEN 7
WHEN 5 THEN 8
WHEN 6 THEN 9
WHEN 7 THEN 10
WHEN 8 THEN 11
WHEN 9 THEN 12
WHEN 10 THEN 13
WHEN 11 THEN 14
WHEN 12 THEN 15
WHEN 13 THEN 16
WHEN 14 THEN 17
WHEN 15 THEN 18
WHEN 16 THEN 19
WHEN 17 THEN 20
WHEN 18 THEN 21
WHEN 19 THEN 22
WHEN 20 THEN 23
WHEN 21 THEN 24
WHEN 22 THEN 1
WHEN 23 THEN 2 --2:00 AM Start--

 

END
SET @EndHour = CASE @TimeRange  
WHEN 0 THEN 3 --End of Overall--
WHEN 1 THEN 16 --End of First Shift--
WHEN 2 THEN 3 --End of Second Shift--
WHEN 3 THEN 7 --7:00 AM END--
WHEN 4 THEN 8
WHEN 5 THEN 9
WHEN 6 THEN 10
WHEN 7 THEN 11
WHEN 8 THEN 12
WHEN 9 THEN 13
WHEN 10 THEN 14
WHEN 11 THEN 15
WHEN 12 THEN 16
WHEN 13 THEN 17
WHEN 14 THEN 18
WHEN 15 THEN 19
WHEN 16 THEN 20
WHEN 17 THEN 21
WHEN 18 THEN 22
WHEN 19 THEN 23
WHEN 20 THEN 24
WHEN 21 THEN 1
WHEN 22 THEN 2
WHEN 23 THEN 3 --3:00 AM END--

 

END

SELECT
  Testerline1_CycleTimes.Station
  ,Testerline1_CycleTimes.VCN
  ,Testerline1_CycleTimes.StationEntry
  ,Testerline1_CycleTimes.StationExit
  ,Testerline1_CycleTimes.InStationTime
  ,Testerline1_CycleTimes.Process1Start
  ,Testerline1_CycleTimes.Process1Stop
  ,Testerline1_CycleTimes.Process1Time
  ,Testerline1_CycleTimes.Process2Start
  ,Testerline1_CycleTimes.Process2Stop
  ,Testerline1_CycleTimes.Process2Time
  ,Testerline1_CycleTimes.PrevStageWait
  ,Testerline1_CycleTimes.NextStageFull
  ,Testerline1_CycleTimes.InStationOT
  ,Testerline1_CycleTimes.Process1OT
  ,Testerline1_CycleTimes.Process2OT
FROM
  Testerline1_CycleTimes
WHERE
  Testerline1_CycleTimes.Station LIKE @Station
  AND Testerline1_CycleTimes.StationEntry Between cast(convert(varchar,@Start,112)+' 5:00 am' as datetime) and cast(convert(varchar,@End,112)+' 5:00 am'     as datetime)
  AND DATEPART( hh , Testerline1_CycleTimes.StationEntry ) >= @StartHour
  AND DATEPART( hh , Testerline1_CycleTimes.StationEntry ) < @EndHour

Answer 14

Can you explain the meaning of the case part you have above?

---

Many Thanks & Best Regards, Hua Min

Answer 15

I have 4 parameters the user specifies before the program runs. First is the pick the Station, then they pick the start date, then the end date. The last one is a drop down box called shift. This lets the user pick "Shift"; either Overall (6:00AM - 3:00AM), First(6:00 AM - 4:00 PM) Second(4:00 PM - 3:00AM), or set one hour increments( 1-2, 3-4, 5-6, etc...). For That parameter I specified the values as 0 - 23. The case part means when the user clicks on say First Shift, that matches to the number 1 in my query; therefore it will return data between the hours of 6:00AM - 4:00PM. The way the program understands this is my final two lines in my program look at the hour portion of the time, to ensure the data being returned is within those hour ranges.

This is where my problem starts; the user first picks the start and end dates which restrict my data to 12:00AM - 00:00 AM. If the user selects 12:00-1:00AM, 1:00-2:00AM, 2:00-3:00AM, Second Shift, or Both Shifts, it is unable to retrieve the data all the way to 3:00AM because it is outside the bounds of my first two date range parameters. The best it will do is return results, but they will be the wrong numbers.

For example: Start Date: 2/20, End Date: 2/21, Shift: 1:00AM-2:00AM. This will return 2:00-3:00AM data from 2/20 NOT 2/21 because the program cannot read data past midnight on 2/21 because it won't go past 0:00:00.

Answer 16

Thanks for all your help guys but I figured it out!

I had to use DateAdd("h",5,Parameters!Start.Value)

I had to put that in the parameters section of my Dataset, the whole time I was trying to add things to the query instead.