Skip to Main Content
  • Questions
  • How do I Declare/Define a date field in a query

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Tanguy.

Asked: December 18, 2018 - 10:32 am UTC

Last updated: December 18, 2018 - 12:29 pm UTC

Version: PL/SQL Release 11.2.0.4.0 - Production

Viewed 1000+ times

You Asked

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

and Chris said...

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!

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.