Professional Web Applications Themes

Quick Select Question - MySQL

Hi All, I have a column in my table with values like this: CBMON3.03122005 WESAT1.04262006 TRSAT3.01282006 .. .. .. I want to create a select statement that will allow me to retreive records within a range of dates. Basically the user will enter a date range. I will create the right half of the column based on their input ("03122005" or "01282006"). However, if I use > or < with a %01282006, it gets everything from the database........ This select does not work: select * from table_1 where substring(column_1,8) > '01282006'; Any suggestions? Thanks....

  1. #1

    Default Quick Select Question

    Hi All,

    I have a column in my table with values like this:

    CBMON3.03122005
    WESAT1.04262006
    TRSAT3.01282006
    ..
    ..
    ..

    I want to create a select statement that will allow me to retreive
    records within a range of dates.

    Basically the user will enter a date range. I will create the right
    half of the column based on their input ("03122005" or "01282006").

    However, if I use > or < with a %01282006, it gets everything from the
    database........

    This select does not work:

    select * from table_1 where substring(column_1,8) > '01282006';

    Any suggestions?

    Thanks.

    amerar@iwc.net Guest

  2. #2

    Default Re: Quick Select Question

    [email]amerariwc.net[/email] wrote:
    > Hi All,
    >
    > I have a column in my table with values like this:
    >
    > CBMON3.03122005
    > WESAT1.04262006
    > TRSAT3.01282006
    > .
    > .
    > .
    >
    > I want to create a select statement that will allow me to retreive
    > records within a range of dates.
    >
    > Basically the user will enter a date range. I will create the right
    > half of the column based on their input ("03122005" or "01282006").
    >
    > However, if I use > or < with a %01282006, it gets everything from the
    > database........
    >
    > This select does not work:
    >
    > select * from table_1 where substring(column_1,8) > '01282006';
    >
    > Any suggestions?
    >
    > Thanks.
    >
    Put your dates in a separate column of type DATE.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: Quick Select Question

    <amerariwc.net> wrote in message
    news:1138741578.100535.52650g14g2000cwa.googlegro ups.com...
    > However, if I use > or < with a %01282006, it gets everything from the
    > database........
    Only the LIKE predicate uses "%" wildcard.
    > This select does not work:
    >
    > select * from table_1 where substring(column_1,8) > '01282006';
    For this reason, I try to abbrevate dates in YYYYMMDD format when I have to
    encode them as a string. It's much easier to do sorting and inequality
    comparisons that way. It doesn't mean you have to make your users input
    strings that way, and you don't have to show dates that way in output. But
    for doing comparisons internally to your code, it's easier to make them
    consistent in this format.

    You could also reformat the string on the fly:
    concat(substring(column_1, 12), substring(column_1, 8, 2),
    substring(column_1, 10, 2))
    converts a string in MMDDYYYY format to YYYYMMDD format. But it is more
    work than you'd need to do if you stored the strings in YYYYMMDD format
    consistently.

    If users input strings in a different format, you may have to reformat the
    strings before using them in SQL comparisons. But this is advisable anyway,
    to make sure the user has entered something that can be interpreted as a
    valid date.

    You can also use the STR_TO_DATE() function of MySQL to convert to a real
    date value:
    STR_TO_DATE('<user-input>', '%m%d%Y')

    See STR_TO_DATE at
    [url]http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html[/url]

    Regards,
    Bill K.


    Bill Karwin Guest

  4. #4

    Default Re: Quick Select Question


    I guess you may be missing my point. I can reformat dates, that is not
    the issue.

    The issue is, then the column which I am comparing against has a date
    embedded in the string: TRSAT1.03052006

    When I try and run a range select against that column, using a
    substring to try an isolate the date, I get the entire database. I
    tried running this query:

    select * from t1 where substring(c1,8) > '2006-01-05' AND
    substring(c1,8) < '2006-01-31';

    That gave me the entire database........

    Any suggestions on queries?

    Thanks,

    Arthur

    amerar@iwc.net Guest

Similar Threads

  1. very quick question
    By quiero mas in forum Coldfusion Database Access
    Replies: 6
    Last Post: November 11th, 05:47 AM
  2. quick MX question....
    By Frances Del Rio in forum Macromedia Flash
    Replies: 3
    Last Post: April 21st, 01:54 PM
  3. Quick question
    By Karen in forum ASP
    Replies: 19
    Last Post: November 24th, 01:35 PM
  4. A Quick Question
    By Trevor Neilson in forum Macromedia Flash
    Replies: 2
    Last Post: September 30th, 09:05 PM
  5. QUICK SSA QUESTION
    By Ken in forum AIX
    Replies: 2
    Last Post: July 14th, 04:08 AM

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