# Query Help: select certain time window over 2 days **URL:** \ **Category:** Ignition\ **Created:** [September 26, 2018, 2:40pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836 "2018-09-26T14:40:47Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [September 26, 2018, 2:40pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/1 "2018-09-26T14:40:47Z")
I have some tags that query production data from a database. I have two shifts and the data has a ‘shift’ column which contains either a 1 or 2. I can easily where clause the shift = 2, but I only need the data from the last shift which is where my trouble comes in. The shift starts at 4pm and ends 2:30 am the next day. Every query I write using things like ```auto where shift = 2 and (DATE(t_stamp) = CURDATE() and HOUR(t_stamp) > 16 ) or (DATE(t_stamp) = DATE_ADD(CURDATE(),INTERVAL -1, DAY)) ``` Doesn’t work because of the change in CURDATE() after midnight. Any ideas for a better way to do this?? Thanks ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [February 15, 2022, 7:22pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/2 "2022-02-15T19:22:52Z")
What did you end up doing? I am thinking I will do like ```auto Mytable.tstamp > 'dateadd(hour,23,dateadd(day,-1,getdate()))' and Mytable.tstamp < 'dateadd(hour,7,getdate()))' ``` just not sure how to dump the minutes, or if it does it ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [February 15, 2022, 7:27pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/3 "2022-02-15T19:27:20Z")
I used a tag that holds the shift start time, then I use ```auto where t_stamp > :shiftStart ``` ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [February 15, 2022, 7:27pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/4 "2022-02-15T19:27:45Z")
oh and then shiftstart is like a tag? or is there a way to do a dropdown for three shifts, but provide two different times as parameters for start and end? For mine, I think a dropdown to pick and send the start and end times would be best for me. I guess I can use a case, and just send 1, 2, 3 and then use static parameters for the starts and ends ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [February 15, 2022, 7:46pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/5 "2022-02-15T19:46:09Z")
I got a cannot coerce value edit:My tag is formatted, but it has a datetime type ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/1/b1e3aafc454e663af26cf003b7a0cf6e3be092e0.png) is there an easy way to convert this in the query? ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [February 15, 2022, 7:50pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/6 "2022-02-15T19:50:44Z")
I use the built in ignition schedules to determine the shift start and end times. I have a gateway timer script that writes to `shift active` boolean tags. [Shift Work Schedules - Ignition - Inductive Automation Forum](https://forum.inductiveautomation.com/t/shift-work-schedules/19848/10) ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [February 15, 2022, 7:51pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/7 "2022-02-15T19:51:58Z")
> [@zacharyw.larson](#): > > My tag is set for datetime, but it has a datetime type Not following what you are saying. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [February 15, 2022, 7:59pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/8 "2022-02-15T19:59:29Z")
Is this true? Converting a `DATEIME` to a `DATE` and then back to a `DATETIME` again sets the time to midnight because if so, ```auto Mytable.tstamp >= dateadd(hour,23,dateadd(day,-1,convert(datetime,convert(date,getdate()))) and Mytable.tstamp <= dateadd(hour,7,convert(datetime,convert(date,getdate()))) ``` ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [February 15, 2022, 8:25pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/9 "2022-02-15T20:25:02Z")
Is that a query or an expression? I’m not familiar with `dateadd` or `convert`. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [February 15, 2022, 8:27pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/10 "2022-02-15T20:27:35Z")
query Microsoft SQL ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [February 15, 2022, 8:45pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/11 "2022-02-15T20:45:44Z")
Usually you need shift start and end times in multiple places. It is much more ergonomic to just pass the dates as parameters to the query from a tag value, which is where you were having the type error? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [February 15, 2022, 8:49pm UTC](https://forum.inductiveautomation.com/t/query-help-select-certain-time-window-over-2-days/19836/12 "2022-02-15T20:49:45Z")
I don’t want to make another tag, and I was not sure how to convert that format in sql Mine isn’t a sliding window though. It is made of static shift times. Update 2 weeks later, I meant to also say that I had solved the issue I had with this, and I use this technique often now. ```auto dateadd(hour,7,convert(datetime,convert(date,getdate()))) ```