SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Date / Query

  1. #1
    SitePoint Zealot
    Join Date
    Jul 2011
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date / Query

    Hello,

    I retrieve a date:

    Code:
    2011-04-30 17:30:35
    Now, I'd like to query another table and get all the records WHERE year = 2011 and month = 04.

    In other words, based on a date retrieved, I'd like to find all records that are of the same year/month.

    I have absolutely no idea where I shoudl start to do this...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    when you say "i retrieve a date" what does that mean?

    are you retrieving it in an application language like php? and are you sure there is only one row that will be returned? and are you happy to do your process using two separate queries?

    if so, you have multiple date functions in php which will allow you to extract dates

    when you generate the conditions for the query that you want to run, make sure you generate a date that is equal to the first day of the month of the retrieved date, and a second date that is equal to the first day of the following month

    for example,
    Code:
     WHERE somedate >= '2011-04-01'
       AND somedate  < '2011-05-01'
    notice it's "greater than or equal to" the first date, but it's "less than" the second date

    that way, you won't ever have to worry about february 29th
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2011
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "I retrieve a date" == I get it from a table based on an id, provided by user input.

    The format I posted is the one that is used by the db (I mean: it looks like that in the table).

    Do I have to worry about hours or not?

    Thanks a lot Rudy

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    FYI it does ~not~ look like that in the table, that's just the default presentation format (mysql's internal date format is not readable by humans)

    so are you happy running two queries, one to get the date from one table and a second query for the other table, or would you rather not do it in one query?

    no, you do not have to worry about the hours
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2011
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Doing it in one query would be an improvement.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    so could you please show the first query, and at least the template of the second query without the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •