Query Help Please! Desperately

judered · #1 (**permalink**) 08-26-08, 03:51

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).

gvee · #2 (**permalink**) 08-26-08, 03:56

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'

judered · #3 (**permalink**) 08-27-08, 03:58

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!

healdem · #4 (**permalink**) 08-27-08, 04:47

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.

r937 · #5 (**permalink**) 08-27-08, 07:10

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

Spudhead · #6 (**permalink**) 09-01-08, 08:59

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech