INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

sql query today plus 3

sql query today plus 3

(OP)
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'

RE: sql query today plus 3

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.

RE: sql query today plus 3

You can simply try:
Where sched_arrive_early = CURDATE() + 3


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.

RE: sql query today plus 3

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.

RE: sql query today plus 3

(OP)
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]

RE: sql query today plus 3

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:

CODE

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

Bye, Olaf.

RE: sql query today plus 3

(OP)
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

RE: sql query today plus 3

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.

RE: sql query today plus 3

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

RE: sql query today plus 3

(OP)
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

RE: sql query today plus 3

I usually start with the hard-coded values:
Where stop.sched_arrive_early between '05/20/2016' and '05/24/2016'

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
dateadd(dd, X, datediff(dd, 0, getdate()))

Or you can try something like:

Where stop.sched_arrive_early between CONVERT(date, GETDATE()) and CONVERT(date, GETDATE() + 4)



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.

RE: sql query today plus 3

(OP)
got it :)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close