CODEWITHSUNDEEP
oracle-databaseoracle-sqldevelopersqlplusoracle12c
AaronLS
AaronLS

Reputation: 38367

Appropriate way to capture dates in variables?

Oracle SQL*Plus (and so SQL Developer) doesn't support declaring variables as date (Citation).

So I can select into a varchar, but it requires I convert to a string. Then it's not really a date anymore and I can't do date arithmetic on it.

What is the appropriate way to work with date variables? I'm using variables instead of declares at the start of a BEGIN/END because I need them available outside of the block to make selects easy to display.

clear screen;
variable someid number; 
variable somedate varchar;
exec :someid := 1234; 

Begin
select c.some_timestamp into :somedate from someschema.sometable c
where c.someid = :someid ;
end;
/

select :somedate from dual;

Upvotes: 0

Views: 51

Answers (2)

APC
APC

Reputation: 146239

This does more or less the same thing:

declare
  somedate date;
Begin
  select c.some_timestamp into somedate 
  from someschema.sometable c
  where c.someid = &someid ;
  dbms_output.put_line(somedate);
end;
/

Obviously you have the need to enable SERVEROUTPUT before running the script but you gain the ability to work with somedate as a date without needing to cast it from a string all the time.

Ultimately it comes down to the details of what you're trying to do. I find it's increasingly rare that I work interactively with SQL*Plus scripts these days: I use an IDE worksheet to develop them and then some shell program executes them autonomously. You say you are using SQL Developer, so you're probably on the same journey.

Upvotes: 0

PKey
PKey

Reputation: 3841

You'll need to use to_date or TO_TIMESTAMP if you want to do arithmetic.

Apparently it is SqlPlus limitation. Below is an example: 

variable my_date varchar2(30)

exec :my_date := '12-04-2017';

select * from mytable where date_col = to_date(:my_date,'dd-mm-yyyy');

Take a look here Declare a variable of type DATE using var

Upvotes: 2

Related Questions