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

Date function in Mysql Query

Date function in Mysql Query

(OP)
Hi there,

I'm wondering if it's possible to do a certain query in mysql without having to write multiple queries. I have a site that has some travel trips and store the trip date range in 2 fields: date_start and date_end

Let's for example assume the following trips:
1 - Hawaii Start: 2016-01-01 End: 2016-01-31
2 - Mexico Start: 2016-02-01 End: 2016-02-29
3 - Caribbean Start: 2016-03-01 End: 2016-03-31

I'm ordering these by date_start ASC but of course once the Hawaii trip is over it would still show up naturally because the start date is still before the others. I need it to stay in the list for only 1 or 2 months (to show people what kinds of trips the company does). Eventually based on date it will need to automatically fall off. I do have another field in the DB for visability to switch from visible to hidden but if that is forgotten I need this to eventually disappear. Once the old trip has come and gone though I need to show it at the end of the list or at least more importantly, show the next trip in the proper order. I'm thinking this will need to likely just be 2 different queries vs 1 but still need someway of coding my query to grab a trip where the date_end is within the last 2 months from the current time of the query. If this is possible with one call instead that would be ideal as I have navigation links that are auto-populated from this to show the whole list and then some pages that only grab the next 2 in the list that are upcoming. Any way to do this? If it's not possible to do as one call then that's just the way it is but I need the date thing to work if that's possible. Thanks.

RE: Date function in Mysql Query

Hi

Like this ? Only events that are visible and ended less than 30 days ago, with ongoing and future events first, ended events last.

CODE --> SQL

select
*

from spyderix

where visible
and datediff(end_date, current_date) > -30

order by end_date < current_date, start_date 

Feherke.
feherke.ga

RE: Date function in Mysql Query

(OP)
I will try that out and get back to you. Thanks smile

NATE

RE: Date function in Mysql Query

(OP)
That works! I tweaked it slightly to:

SELECT * FROM `trips` WHERE `viz` = 'Y' AND DATEDIFF(`date_end`, '2016-01-01') > -30 ORDER BY `date_end` < '2016-01-01', `date_end`

One thing I find curious and can't wrap my head around is the ORDER BY statement. Don't get me wrong, this SQL works exactly in the correct order but this part: `date_end` < '2016-01-01' looks to be written as if any number less than 2016-01-01 would show first yet it's the opposite. I guess it's a date thing and doesn't work like a strict number. A `date_end` less than 2016-01-01 is actually a date "after" it??? Seems backwards.

NATE

RE: Date function in Mysql Query

Hi

Quote (NATE)

`date_end` < '2016-01-01' looks to be written as if any number less than 2016-01-01 would show first yet it's the opposite.
Putting that expression into select's field list explains it : it results
  • 0 when the end_date before current_date is false
  • 1 when the end_date before current_date is true
So is just integer ordering. But indeed looks abit counterintuitive.

Feherke.
feherke.ga

RE: Date function in Mysql Query

(OP)
Ah, that makes perfect sense. Thanks a ton!

NATE

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