SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select as where not working!

    Why desn't this work?

    SELECT CONCAT(RIGHT(dt,4),'-',MID(dt,4,2),'-',LEFT(dt,2)) AS dateavail FROM atable WHERE dateavail = '2006-06-01'

    (dt is a date in format dd/mm/yyyy - the concat bit works fine)

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [MySQL][ODBC 3.51 Driver][mysqld-4.1.19-standard-log]Unknown column 'dateavail' in 'where clause'

    Also, if I get it to work, how do I convert the 2 strings to date so I can look for < and > ?

    cheers

    monkey
    monkey - the rest is history

  2. #2
    SitePoint Wizard chris_fuel's Avatar
    Join Date
    May 2006
    Location
    Ventura, CA
    Posts
    2,750
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aliases won't work, because the first thing that is looked at is the actual existant column names (not aliases) with regards to the where clause. The alias is assigned only if an actual entry is found. While not MySQL, the following mailing list entry:

    http://archives.postgresql.org/pgsql...6/msg00262.php

    explains it.

  3. #3
    SitePoint Wizard chris_fuel's Avatar
    Join Date
    May 2006
    Location
    Ventura, CA
    Posts
    2,750
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, try this instead:

    SELECT FROM atable WHERE CONCAT(RIGHT(dt,4),'-',MID(dt,4,2),'-',LEFT(dt,2)) = '2006-06-01'

    see if that works.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT dateavail
      FROM ( 
           SELECT CONCAT(RIGHT(dt,4)
                     ,'-',MID(dt,4,2)
                     ,'-',LEFT(dt,2)) AS dateavail 
             FROM atable 
           ) as T 
     WHERE dateavail = '2006-06-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    SELECT dateavail
      FROM ( 
           SELECT CONCAT(RIGHT(dt,4)
                     ,'-',MID(dt,4,2)
                     ,'-',LEFT(dt,2)) AS dateavail 
             FROM atable 
           ) as T 
     WHERE dateavail = '2006-06-01'
    You people are geniuses - thanks both
    monkey - the rest is history

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    better than that CONCAT() mess is STR_TO_DATE(dt,'%m/%d/%Y')

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck
    better than that CONCAT() mess is STR_TO_DATE(dt,'%m/%d/%Y')
    that'll work if you remove the slashes and swap the %m and %d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard boxhead's Avatar
    Join Date
    Feb 2004
    Location
    UK
    Posts
    1,040
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    that'll work if you remove the slashes and swap the %m and %d
    I have this:

    SELECT * FROM (SELECT *, STR_TO_DATE(dt,'%d/%m/%Y') AS dateavail FROM myTable ) as T WHERE dateavail <= 20060619

    which appears to work - it didn't like not having the /'s (the date in the db is dd/mm/yyyy)

    monkey
    monkey - the rest is history

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, sorry, my bad, i got really confused by the offsets in your CONCAT

    at least i picked up on the fact that it was dd then mm
    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
  •