1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL Query Challenge

Discussion in 'Databases' started by Weirfire, Nov 11, 2005.

  1. #1
    Before I figured out what the timestamp feature was I used a seperate field to store the day month and year. I believed strongly in making things difficult for myself and now I am paying the price.
    SEMrush
    Here is a query for those who like a challenge.

    I want to display all entries in the database which are after a certain date and also display all entries which are before a certain date.


    The table goes something like

    id title day month year

    where day is a 2 digit number 00 - 31
    where month is a 2 digit number 01 - 12
    and year is 4 digit number 1999-2006

    If you think you can manage this one please help me out! :)


    Thanks!
     
    Weirfire, Nov 11, 2005 IP
    SEMrush
  2. Dejavu

    Dejavu Peon

    Messages:
    917
    Likes Received:
    53
    Best Answers:
    0
    Trophy Points:
    0
    #2
    How about altering the table to add a timestamp column, then write a php script to convert all your seperate fields to the timestamp. Then you can easily do any queries, and it will be much faster..
     
    Dejavu, Nov 11, 2005 IP
    dct likes this.
  3. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #3
    Do something like
    
    select * from table 
    where 
    20050101 => (year * 10000 + month * 100 + day) and
    20051231 <= (year * 10000 + month * 100 + day)
    
    Code (sql):
    Than slap your self around the head with a dead fish for not using a proper date field
     
    dct, Nov 11, 2005 IP
  4. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #4
    I would love to but there are scripts in place that already use the format in the database. :(

    It's likely to be more hassle changing the database than it is working round it.


    Thanks for your suggestion though.
     
    Weirfire, Nov 11, 2005 IP
  5. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #5
    What Dejavu says is the best idea, you could also do it via SQL to save you asking us a PHP question :D
     
    dct, Nov 11, 2005 IP
  6. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #6
    Thanks dct :)
     
    Weirfire, Nov 11, 2005 IP
  7. JamieC

    JamieC Well-Known Member

    Messages:
    226
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    138
    #7
    Why don't you add a timestamp field using your favourite scripting language, but leave in your legacy fields as well.

    Any new pages in your site, make sure you use the timestamp field and gradually phase out your old fields as you maintain your site.

    Once you are satisfied that you have purged all old code, you can remove those fields from the table.

    It's all about compromise :-D
     
    JamieC, Nov 11, 2005 IP
  8. hnn

    hnn Peon

    Messages:
    91
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Like JamieC said, convert the site slowly. Take one page at the time...

    Or "Rewrite and do it again =)"
     
    hnn, Nov 11, 2005 IP
  9. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #9
    Fantastic idea JamieC. I'm glad someone has there thinking hat on :eek:
     
    Weirfire, Nov 11, 2005 IP