SQL Server Performance Forum – Threads Archive

Date lying between 2 days

In the following query i want to get the orders for previous day and not the current day.
I am unable to frame the date range for this, so i am leaving it blank.I tried so many ways but what i need is date starting from zero hrs yesterday till zero hrs today.I need report lying for the day before today.
Thanks I have the following query
SELECT O.work_order_id,O.LOAN_NUMBER,WEB_SUBMIT_DT
FROM OA_EST_HDR A (NOLOCK)
JOIN grasscutordercompletion B (NOLOCK) ON A.ORDER_ID = B.grasscutorderID
WHERE b.SUBMIT_STATUS = 0 AND O.ORDER_STATUS = 13
and day(WEB_SUBMIT_DT) = I have the date in the following format "2004-01-28 08:49:00"

---

I am assuming by "0 hours" you mean 12 AM midnight and also that you need it between 2 days. Try this in your WHERE clause … WHERE b.SUBMIT_STATUS = 0 AND O.ORDER_STATUS = 13
and day(WEB_SUBMIT_DT) BETWEEN dateadd(dd,datediff(dd,0,getdate()-1),0) AND dateadd(dd,datediff(dd,0,getdate()),0) – Tahsin

---

Thanks for your query tahsin
I am able to set the dates using the following query but how to make the dates dynamic
or put it in a variable and just put the required dates and get the results or set it as a job to
schedule it.
SELECT O.work_order_id,O.LOAN_NUMBER,o.WEB_SUBMIT_DT
FROM OA_EST_HDR A (NOLOCK)
JOIN ordercompletion B (NOLOCK) ON A.ORDER_ID = B.gcutID
JOIN ORDERS O (NOLOCK) ON O.ORDER_ID = B.gcutID
WHERE b.SUBMIT_STATUS = 1 AND O.ORDER_STATUS = 13 and b.adjusted=0 and O.source is null
and (O.WEB_SUBMIT_DT BETWEEN ‘2006-03-28’ AND ‘2006-04-03 23:59:59.997’)

---

Your code is working great with the following changes.You have to use day rather than dd
give the date range a day previous to current day.
Thanks where WEB_SUBMIT_DT BETWEEN dateadd(day, datediff(day, 0, getdate()), -1) and dateadd(day, datediff(day, 0, getdate()), -1)

---

dd and day are synonomous AFAIK. You can test this in QA: Using "dd":
print dateadd(dd,datediff(dd,0,getdate()-1),0)
print dateadd(dd,datediff(dd,0,getdate()),0) Using "DAY":
print dateadd(day, datediff(day, 0, getdate()), -1)
print dateadd(day, datediff(day, 0, getdate()-1), 0)
print dateadd(day, datediff(day, 0, getdate()), 0)
BTW, your BETWEEN statement is not written properly because you are comparing between the same 2 dates. – Tahsin

---

Yeh I tried in my query Analyzer and both of them are returning same results.
Can you tell me how the above query be corrected.
here is my query and (O.WEB_SUBMIT_DT BETWEEN dateadd(day, datediff(day, 0, getdate()), -1) and
dateadd(day, datediff(day, 0, getdate()), 0)) Thanks

---

Is your query returning what you need it to return? If not what result are you expecting and what needs to be "corrected"? The 1st submission on 04/03/2006 : 21:15:33 is incorrect because your days are the same. "WEB_SUBMIT_DT BETWEEN dateadd(day, datediff(day, 0, getdate()), -1) and dateadd(day, datediff(day, 0, getdate()), -1)"
I don’t see anything particularly wrong with your recent submission (04/05/2006 : 13:01:44 ): "and (O.WEB_SUBMIT_DT BETWEEN dateadd(day, datediff(day, 0, getdate()), -1) and
dateadd(day, datediff(day, 0, getdate()), 0))" – Tahsin

---

Yeh that one goes if you’re calculating for the current day.
( SUBMIT_DT BETWEEN ‘2006-03-28’ AND ‘2006-04-03 23:59:59.997’)

---

… or eliminate the time processing: ( CONVERT(VARCHAR(10), SUBMIT_DT, 120) BETWEEN ‘2006-03-28’ AND ‘2006-04-03’)) You probably should not use this in case there is a covering index for the SUBMIT_DT field: the index wouldn’t get used because you’re processing the value from the column.

---

… and on the other hand: why would a SUBMIT_DT column record the time of day?

---

Adriaan
I am not concern about the time but i was using time to calculate hours in a day from 00 to 24hrs if i have to get the records for just one day.Yes there are so many other ways i can calculate the day but i found this a good one so i used it.
thanks

---

]]>