Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 8 of 8
  1. #1
    Member
    Join Date
    Aug 2005
    Location
    United Kingdom
    Posts
    38
    Member #
    11128
    I have got date stored in MySQL in this format "Sat, 22 Oct 2005 - 19:30:00" and I want to write a query to select ALL records with DATE coming AFTER the CURRENT DATE or TODAY. In other words I want to select all future dates only. Can anyone help ?

    http://www.binarytrends.com

  2.  

  3. #2
    Senior Member
    Join Date
    Jul 2005
    Location
    S.Wales, UK
    Posts
    250
    Member #
    10742
    MySQL stored dates in the format yyyy-mm-dd
    The format that it is displayed on the screen is different to how it is stored.

    Now() will give you the current date in SQL.

    So yourdate > Now() should give you what you want.
    Paul Creedy
    Visit my community site at: www.rhonddacynontaff.com
    My hosting: www.rcthosting.com
    My Content Management product: www.dynamiccontenteditor.com

  4. #3
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    Is this particular column defined as 'date' column, or are you storing the data as CHAR/VARCHAR?
    S. Rosland

  5. #4
    Member
    Join Date
    Aug 2005
    Location
    United Kingdom
    Posts
    38
    Member #
    11128
    date query

    It is stored in VARCHAR.


    Quote Originally Posted by rosland
    Is this particular column defined as 'date' column, or are you storing the data as CHAR/VARCHAR?

  6. #5
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    To enable MySQL to sort dates/ find ranges between values, you have to convert the column (to the best of my knowledge). Right now, all values are stored as strings, which makes it impossible to execute any sort of arithmetic on the data.

    You will at least have to alter the data to some sort of integer if you want to run comparisons etc. If you convert it to 'date' format, you open it up to all the fairly advanced date operations/manipulations that MySQL supports.

    If you check out their online manual, you will find a lot of ways to convert date formats from other systems.
    If you find nothing suitable, you will have to write a script of your own, to extrapolate the date info from your strings, and reinsert it in a way MySQL recognizes as dates.
    S. Rosland

  7. #6
    Senior Member
    Join Date
    Jul 2005
    Location
    S.Wales, UK
    Posts
    250
    Member #
    10742
    If you store it as a string (VARCHAR) then yes, you need to convert to a date format for comparisons.

    However there is a MySQL Date type field format. If the date is being stored in a field of DATE type then no conversion should be needed. This is the method that I use since you do not need to do secondary conversions.

    Dates are always a nightmare with databases as there are a few ways of storing them and we all have our favourite ways: VARCHAR, DATE, DATETIME, etc
    Paul Creedy
    Visit my community site at: www.rhonddacynontaff.com
    My hosting: www.rcthosting.com
    My Content Management product: www.dynamiccontenteditor.com

  8. #7
    Member
    Join Date
    Aug 2005
    Location
    United Kingdom
    Posts
    38
    Member #
    11128
    I found the solution

    Thanks guys. I finally found the solution to it without having to change the data type of my table field. Here is how it can be done :

    DATEDIFF(str_to_date(right(my_date_field, 22),'%d %b %Y'),CURDATE())>0

    Shahid
    http://www.binarytrends.com

  9. #8
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    Happy to see you found a MySQL-solution.

    Whenever I need to use dates, I always convert them to a format easily recognized by MySQL. That way, date manipulation is easier (less complex/verbose code) and speedier execution.

    I was not aware of the str_to_date() call.

    Glad it works to your satisfaction.
    S. Rosland


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 09:24 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com