SQl query with dates from ASP page

[Bastien]

Hi guys,

I have a problem with a page where I request a StartDate and an EndDate for an online report. However when the dates are the same or the EndDate is blank and defaults to the StartDate, the SQL query won't work. I assum this is because the time portion of the date fields defaults to 12:00:00AM and therefore the result is null. How can I adjust to make the EndDate time portion default to 11:59:59 PM? I tried adding the time on at then end...

ie EndDate = EndDate & "11:59:59 PM" but this doesn't seem to work for the query. I am using an Oracle DB to query from.

Any suggestions??

Bastien

 

[jonscott8]

This sounds more like a SQL problem. Post the actual query that is being executed, specifically the where condition.

It also might be helpful if you post the ASP code you are using to execute the query.

Jon Hawkins

 

[Bastien]

strEndDD = Request.Form ("EndDD&quot 'get the end date
strEndMM = Request.Form ("EndMM&quot
strEndYY = Request.Form ("EndYY&quot

strStartDD = Request.Form ("StartDD&quot 'get the start date
strStartMM = Request.Form ("StartMM&quot
strStartYY = Request.Form ("StartYY&quot

strEnd = strEndDD & "-" & strEndMM & "-" & strEndYY
strStart = strStartDD & "-" & strStartMM & "-" & strStartYY
'Response.Write strEnd
if not IsDate(strEnd) and not IsDate (strStart) then 'if empty then create the form to request the dates
call CreateForm
elseif IsDate(strStart) and not IsDate(strEnd) then 'if no end date
strEnd = strStart 'the end date and start date ae the same
elseif isDate(strEnd) and not isDate(strStart) then
strStart = strEnd
end if


if isDate(strStart) and IsDate(strEnd) then 'everything ok and call the query
call GetInfo
end if

sub GetInfo()
'create the query

strGetTaxInfo = " select Client_code,tax_form, run_flag, last_run_date, send_out_flag, send_out_date "
strGetTaxInfo = strGetTaxInfo & " from tax_table where "
strGetTaxInfo = strGetTaxInfo & " tax_year = 2000 and "
strGetTaxInfo = strGetTaxInfo & " send_out_date >= to_date('" & strStart & "','DD-MM-YYYY') and"
strGetTaxInfo = strGetTaxInfo & &quot; Send_out_date <= to_date('&quot; & strEnd & &quot;','DD-MM-YYYY')&quot;
strGetTaxInfo = strGetTaxInfo & &quot; group by client_code,tax_form, run_flag, last_run_date, send_out_flag, send_out_date&quot;

 

[jonscott8]

I'm not an Oracle guru, but how about:

strEnd = strEndYY & strEndMM & strEndDD
strGetTaxInfo = strGetTaxInfo & &quot; TO_CHAR(Send_out_date,'YYYYMMDD') <= ' & strEnd & &quot;''

or:

strEnd=strEnd & &quot; 23:59:59&quot;
strGetTaxInfo = strGetTaxInfo & &quot; Send_out_date <= TO_DATE('&quot; & strEnd & &quot;','DD-MM-YYYY HH24:MI:SS')&quot; Jon Hawkins