Problems prompting for a date because of a timestamp in same field

[ba543]

I am currently trying to write an SQL statement that prompts for information between two time periods. I am trying to prompt between two dates, unfortunately I have a timestamp field within the date field. I am not sure how to be able to pull the date field out or to be able to prompt the date field and time field within the same prompt. Below is a look at the data I'm working with, and below that is the SQL I have created. Any help would be greatly appreciated.
Thanks

ERROR_TIMESTAMP
2004-06-20 00:07:18


Select *
FROM RPT210ERROR_HAND
WHERE (ERROR_TIMESTAMP TO_DATE >= ('06-20-2004','yyyy-mm-dd hh:mm:ss'))
and (ERROR_TIMESTAMP TO_DATE <= ('06-24-2004','yyyy-mm-dd hh:mm:ss'))

 

[BobbaFet]

Have you tried using the same formatting for both dates?
so for example get them both as dd-mm-yyyy hh:mm:ss.

BobbaFet

Everyone has a right to my opinion.
E-mail me at caswegkamp@hotmail.com

http://cwcon.50megs.com/

 

[hilfy]

When working with dates, I usually try to use a parameterized query instead of dynamic SQL. Also, you can use the "Trunc" SQL function to get just the date part of the field. Your SQL will look something like this:

Select * 
FROM RPT210ERROR_HAND
WHERE trunc(ERROR_TIMESTAMP TO_DATE) >= :Start_Date 
  and trunc(ERROR_TIMESTAMP TO_DATE) <= :End_Date

In the Object Inspector, select the "Params" property for your query and for each parameter, set the ParamType to ptInput and the DataType to ftDate. Your code would then look something like this:

MyQuery.ParamByName('Start_Date').AsDateTime := dStartDate;
MyQuery.ParamByName('End_Date').AsDateTime := dEndDate;
MyQuery.Open;

The great thing about doing it this way is that you don't have to do any date conversion as the query will take care of converting the parameters to the correct format. You can also use this method with dynamic SQL if you need to - you just use the "ParamByName" to set the ParamType and DataType of each parameter prior to setting the values.
-Dell