When i was working with MSSQL I could DECLARE a start & End date (or other parameters) to avoid having to change a variable in the query itself.
This is what the table looks like
-------------------- ------ ------
DateTime Action User
-------------------- ------ ------
16/12/2018 20:32:58 PICK John
16/12/2018 23:58:42 DROP John
17/12/2018 2:13:54 PICK George
17/12/2018 10:24:36 PICK Paul
17/12/2018 16:44:25 DROP Paul
18/12/2018 1:23:45 DROP George
The query would be something like this :
DECLARE @StartTime char(10); set @startTime = '2018-12-17'
DECLARE @StopTime char(10); set @stopTime = '2018-12-18'
SELECT DateTime, Action, User
FROM transport
WHERE Time BETWEEN @StartTime AND @StopTime
I have tried several TO_DATE & DEFINE options but i keep getting errors
When you're to_date'ing a string, supply a format mask. Ensure this matches the format of your string:
var st_dt varchar2(12);
var en_dt varchar2(12);
exec :st_dt := '10-DEC-2018';
exec :en_dt := '13-DEC-2018';
with rws as (
select date'2018-11-30' + level dt
from dual
connect by level <= 31
)
select * from rws
where dt between to_date ( :st_dt, 'DD-MON-YYYY' )
and to_date ( :en_dt, 'DD-MON-YYYY' );
DT
10-DEC-2018 00:00:00
11-DEC-2018 00:00:00
12-DEC-2018 00:00:00
13-DEC-2018 00:00:00
If this doesn't work, you'll need to be more specific about what the problem is. I.e. your code and the error you get.
PS - when you're getting errors in Oracle Database, it helps to show us your
Oracle code. Not the MSSQL!