Professional Web Applications Themes

Trouble using SYSDATE() - MySQL

Hi, I have to select elements that are not older than 2 months (=60 days). The table has a standard DATE column with format (YYYY-MM-DD), called 'date'. I tried a lot of queries, one of them is following: SELECT * FROM mytable WHERE date > (SYSDATE()-60); So basically i'm telling mysql to select elements where the date is bigger (= older) than the actual system date diminished with 60 days. But it does't return anything (and yes I have elements newer than 2 months in the table :-) I suppose something is wrong with the use of SYSDATE. Anyone any ...

  1. #1

    Default Trouble using SYSDATE()

    Hi,

    I have to select elements that are not older than 2 months (=60 days).
    The table has a standard DATE column with format (YYYY-MM-DD), called
    'date'.

    I tried a lot of queries, one of them is following:

    SELECT * FROM mytable WHERE date > (SYSDATE()-60);

    So basically i'm telling mysql to select elements where the date is
    bigger (= older) than the actual system date diminished with 60 days.
    But it does't return anything (and yes I have elements newer than 2
    months in the table :-)

    I suppose something is wrong with the use of SYSDATE. Anyone any
    experience with this ?

    b.coolsaet@gmail.com Guest

  2. #2

    Default Re: Trouble using SYSDATE()


    com wrote:
     

    The user manual has a lot of experience of this and explains how to do
    it.

    Think about it. SYSDATE() "Returns the current date and time as a value
    in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether
    the function is used in a string or numeric context." (quoted from
    manual)

    If you simply substract 60 from this, how is MySQL to know 60 what?
    60 years?
    60 seconds?
    60 bars of chocolate?

    Try:
    SELECT * FROM mytable WHERE date > (SYSDATE()-INTERVAL 60 DAY);

    as the manual suggests
    http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

    Captain Guest

  3. #3

    Default Re: Trouble using SYSDATE()

    I think that it would be better with

    SELECT * FROM mytable WHERE date > adddate(sysdate(), -60)

    Which is a spin off from "date_add(date, -60, DAY)"

    You can swap DAY for anything like:

    MICROSECOND
    SECOND
    MINUTE
    HOUR
    DAY
    WEEK
    MONTH
    QUARTER
    YEAR


    Hope that helps.


    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com... 
    >
    > The user manual has a lot of experience of this and explains how to do
    > it.
    >
    > Think about it. SYSDATE() "Returns the current date and time as a value
    > in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether
    > the function is used in a string or numeric context." (quoted from
    > manual)
    >
    > If you simply substract 60 from this, how is MySQL to know 60 what?
    > 60 years?
    > 60 seconds?
    > 60 bars of chocolate?
    >
    > Try:
    > SELECT * FROM mytable WHERE date > (SYSDATE()-INTERVAL 60 DAY);
    >
    > as the manual suggests
    > http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
    >[/ref]



    Sean Guest

  4. #4

    Default Re: Trouble using SYSDATE()


    Sean wrote:
     
    Why would it be better?
    And why wouldn't you use subdate rather than adddate with a negative
    argument?

    Captain Guest

  5. #5

    Default Re: Trouble using SYSDATE()


    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com... 
    > Why would it be better?
    > And why wouldn't you use subdate rather than adddate with a negative
    > argument?
    >[/ref]

    Sorry, you're right.

    I was implying that this would be a better option than SYSDATE()-INTERVAL 60
    DAY and overlooked "sub_date", which of course makes even more sense.

    SA



    Sean Guest

  6. #6

    Default Re: Trouble using SYSDATE()


    Sean wrote:
     
    > > Why would it be better?
    > > And why wouldn't you use subdate rather than adddate with a negative
    > > argument?
    > >[/ref]
    >
    > Sorry, you're right.
    >
    > I was implying that this would be a better option than SYSDATE()-INTERVAL 60
    > DAY and overlooked "sub_date", which of course makes even more sense.
    >
    > SA[/ref]
    But why is subdate (or date_sub) better than the opure maths approach
    of
    SYSDATE()-INTERVAL 60 ?

    Captain Guest

Similar Threads

  1. Difference between SYSDATE and TO_DATE
    By Tom in forum Oracle Server
    Replies: 10
    Last Post: October 31st, 06:45 AM
  2. function NOW() to return sysdate
    By Neil in forum Oracle Server
    Replies: 3
    Last Post: October 29th, 03:12 PM
  3. Please help! rpm got me into trouble.
    By Juha-Petri in forum Linux Setup, Configuration & Administration
    Replies: 4
    Last Post: October 8th, 04:15 PM
  4. Replies: 1
    Last Post: July 18th, 09:57 PM
  5. Replies: 1
    Last Post: July 17th, 03:23 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139