sql query today plus 3

[rrmcguire]

How would I write a query to pull only the orders where sched_arrive_early = today + 3


So

select * from orders, stop where orders.id = stop.order_id and stop.sched_arrive_early = 't + 3'

 

[Olaf Doschke]

That is DATEADD(day, 3, Getdate())

You'll never hit the exact time, though.

What is the condition for sched_arrive_early? Is it "early", if it is <t+3?

Bye, Olaf.

 

[Andrzejek]

You can simply try:[tt]
Where sched_arrive_early = CURDATE() + 3[/tt]

Keep in mind, this will give you: from midnight today to midnight 3 days from now.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Olaf Doschke]

The overall best query and where clause also depends on the type of stop.sched_arrive_early.
If that is a date, you also would cast DATEADD(day, 3, Getdate()) to a date or use CURDATE()+3, if your MSSQL Server version offers that.

Bye, Olaf.

 

[rrmcguire]

sched_arrive_early is a datetime field, I tried using sched_arrive_early = CURDATE() + 3 and that didn't work, telling me 'CURDATE' is not a recognized built-in function name. [SQLSTATE: 42000 Error Code: 195]

 

[Olaf Doschke]

Just do SELECT DATEADD(day, 3, Getdate()), you'll see that working.
The problem is not the calculation but the comparison. You typically need some BETWEEN comparison of a datetime being in a timespan.
An exception to that rule is, you always store midnight as time portion of the datetime, then it stands for a date rather than some point in time.

So next question: Is the stop.sched_arrive_early always set to a date at midnight? Or perhaps at 12pm?

If midnight is the case DATEADD(dd, DATEDIFF(dd, 0, getdate())+3, 0) will give you midnight of the date in 3 days, so what would then work is:

Where sched_arrive_early = DATEADD(dd, DATEDIFF(dd, 0, getdate())+3, 0)

Bye, Olaf.

 

[rrmcguire]

ok, that seems to work except for 2 records where its pulling the columns where the date is 5/23 and not 5/20, just cannot seem to figure it out why

 

[Olaf Doschke]

That join can't come from that where condition. Most likely comes from another where clause or join condition ORed somehow with this. Or a union, subselect. Any complexity allowing data not fulfilling that condition but others.

You might post your whole query to get a clue. DATEADD(dd, DATEDIFF(dd, 0, getdate())+3, 0) will only result in current date + 3 days midnight and if data is not fulfilling that condition, it has to come in by fullfilling other conditions of your overall query.

Bye, Olaf.

 

[rrmcguire]

after reviewing again that seemed to work...thanks again for your help

 

[rrmcguire]

ok, so there's been a change in this and now am needing between the current day and 3 days after, currently for sched_arrive_early = today + 3

I have stop.sched_arrive_early>=dateadd(dd, 3, datediff(dd, 0, getdate())) and stop.sched_arrive_early<dateadd(dd, 4, datediff(dd, 0, getdate()))

where the second part accommodates for time not equal to 0000. Any help would be appreciated

 

[Andrzejek]

I usually start with the hard-coded values:[tt]
Where stop.sched_arrive_early between '05/20/2016' and '05/24/2016'[/tt]
The dates are at midnight (time 00:00:00), so it will give you the whole day of 20th, 21st, 22nd, and 23rd until midnight.

Now you just need to replace the dates in quotes with the magic of
[tt]dateadd(dd, X, datediff(dd, 0, getdate())) [/tt]

Or you can try something like:
[tt]
Where stop.sched_arrive_early between CONVERT(date, GETDATE()) and CONVERT(date, GETDATE() + 4)[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[rrmcguire]

got it

stop.sched_arrive_early between getdate() and dateadd(dd, 3, datediff(dd, 0, getdate()))