Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Question Unanswered: Query Help Please! Desperately

    i have table which have a date column, in addition to a set of other columns, all what i need a simple select query to get * from myTable where dateColumn value between (Start date) and (End date).

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT col1
         , col2
         , col3
         , date_col
    FROM   table_name
    WHERE  date_col BETWEEN '2008-01-01T00:00:00.000' AND '2009-01-01T00:00:00.000'
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2008
    Posts
    2

    Question

    Quote Originally Posted by georgev
    Code:
    SELECT col1
         , col2
         , col3
         , date_col
    FROM   table_name
    WHERE  date_col BETWEEN '2008-01-01T00:00:00.000' AND '2009-01-01T00:00:00.000'
    Hi George
    Firstly Thanks very much for your prompt answer.
    however I used your method to run, still not working.
    I have 2 tables: clients, clt_orders. is there any error with the statement below?
    sql="select * from clients,clt_orders where clients.ID=clt_orders.client_id and clt_orders.date between '"&request("start_date")&"' and '"&request("end_date")&"' order by clt_orders.date"


    by the way, date format is mm/dd/yyyy. does it matter with this? if so, how can I convert to right format. Thanks again!
    Last edited by judered; 08-27-08 at 05:04.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what does the SQL that youy are sending to the server actually read as
    guessing thats VB or a close relation... do a debug or message box of SQL variable.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by judered
    by the way, date format is mm/dd/yyyy. does it matter with this? if so, how can I convert to right format.
    yes, it matters, it has to be yyyy-mm-dd

    you would convert it into the correct format in whatever language that your are using that produces "&request("start_date")&" -- convert it there first, before sending the query to mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2002
    Posts
    189
    Looks like VBScript ASP to me. So...

    1. This will turn your date into something that MySQL understands:

    Code:
    <%
    function addLeadingZero(s)
    	dim rv : rv = s
    	if len(cStr(s)) = 1 then rv = "0" & s
    	addLeadingZero = rv
    end function
    
    function SQLDate(sDate)
    	dim rv
    	dim sDateDay, sDateMonth, sDateYear
    	if isDate(sDate) then
    		sDateDay = day(sDate)
    		sDateMonth = month(sDate)
    		sDateYear = year(sDate)
    		rv = "'" & sDateYear & "-" & addLeadingZero(sDateMonth) & "-" & addLeadingZero(sDateDay) & "'"
    	end if
    	SQLDate = rv
    end function
    %>
    2. Dropping unvalidated user input into a SQL statement is suicidal. Always check for malicious or plain stupid input before throwing it at your database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •