• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1009
  • Last Modified:

How to use date variables in SELECT Statement within SQLPLUS?

How to use date variables in SELECT Statement within SQLPLUS?
I need to pass to a SQL query some dates obtained from a previous select query.

I have this code:
-----------------------------------------------
#!/bin/ksh
sqlplus -s /nolog > /dev/null 2>&1 <<EOF
connect myusername/mypassword

set echo off
set feedback off
set linesize 500
set pagesize 0
set sqlprompt ''
set term off
set verify off
set trimspool on

column dcolmin new_value begdate noprint
column dcolmax new_value enddate noprint

select mindate dcolmin, maxdate dcolmax from mytable;

spool /mydirectory/myfile.csv

select <my code> from mytable_1 a
where a.check_date between &begdate. and &enddate.
-------------------------------------------------------
This error is written to the output file:
and a.check_date between 26-FEB-10 and 19-MAR-10
                                          *
ERROR at line 21:
ORA-00904: "MAR": invalid identifier
-----------------------------------------------
(Please note that the dates are correct.  Please also note that if the dates
for the check_date are hardcoded, the query works OK:

and a.check_date between '26-FEB-2010' and '19-MAR-2010' - WORKS FINE!

What is a right way to use date variables in SELECT Statement within SQLPLUS?

Thank you!
0
pavelmed
Asked:
pavelmed
  • 3
  • 2
1 Solution
 
mohsajdiCommented:
Check the NLS_DATE_FORMAT parameter
0
 
sandeshjCommented:
To make sure of the format always use TO_DATE like this...

TO_DATE('26/02/2010','MM/DD/YYYY')
0
 
pavelmedAuthor Commented:
I was able to resolve it by first using to_char() to reformat "mindate" and "maxdate" to DD-MM-YYYY,
and then in the second select statement using to_date() to reformat the values back to the date format.
I am assigning points to sandeshi since his suggestion is close to what I was doing.
Thank you!
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sandeshjCommented:
Hi pavelmed,
I'm glad that my answer came close to what you've finally implemented. Just as a gentle reminder, as stated by you above probably you've forgotten to assign the points and close this question.
Thanks,
Sandesh.
0
 
pavelmedAuthor Commented:
Sorry Sandesh,

I did forgot to assign points.  Doing this now.
0
 
sandeshjCommented:
Hey! Thanks.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now