How to use sql "sysdate" in unix?

Shell Programming and Scripting
1

I am use unix shell script to called an sql Script to query data in my shell program.

sqlplus -S /nolog @update.sql

but my script on function "sysdate" not work !!
Could you tel me,How can i use function "sysdate" on unix or can replace the other function in my script to get data in system date.

Ex. my pl update.sql

connect user/pass@server

begin

insert into table_X 
select * from table_aaa m 
where m.insert_date = to_date(sysdate,'dd/mm/yyyy');
commit;

update table_X set x_flag = 'complete'
where x.insert_date = to_date(sysdate,'dd/mm/yyyy');
commit;

end
/
exit
2

Hi,
What errormessage do you receive?
Are you working against an Oracle-database?
If its Oracle then you do not need the to_date function, sysdates return value is already datatype date.

PS.: please use code tags.

3

Some more questions/comments:
-----> What is the type of "insert_date" column in table: table_X

Select  COLUMN_NAME,
          DATA_TYPE
From ALL_TAB_COLUMNS
Where      TABLE_NAME = 'TABLE_X'
         And COLUMN_NAME = 'INSERT_DATE';

If it is CHAR/VARCHAR2, you need TO_CHAR(sysdate,'dd/mm/yyyy')

-----> There is no need to use BEGIN/END in the block you posted!

4

to_date's first argument is of type char, and sysdate is of type DateTime. Assuming that insert_date is also of type Date or DateTime, then:

update table_X set x_flag = 'complete'
where x.insert_date = sysdate;

See sysdate for more information.

5

Big thank Cero ... It work !! , Sysdate in Unix not use Format Date then i use "trunc(sysdate)" :slight_smile:

---------- Post updated at 11:56 AM ---------- Previous update was at 11:55 AM ----------

thank For support ... the type of "insert_date" is date ^^

---------- Post updated at 11:58 AM ---------- Previous update was at 11:56 AM ----------

Thank a lot for information ... my data type is date , then i use trunc(sysdate) it work.

6

Actually, the Unix date can be formatted and even passed as a parameter to an Oracle script.

$
$ cat -n fetch.sql
     1  connect user/password@database
     2  set verify off timing off
     3  select 'The date passed from Unix is ' || &1 as msg
     4  from dual
     5  /
     6  exit
$
$
$ sqlplus -S /nolog @fetch.sql "DATE '`date '+%Y-%m-%d'`'"
 
MSG
--------------------------------------
The date passed from Unix is 21-OCT-11
 
1 row selected.
 
$
$

However, you must understand that this is the client's date, whereas Oracle's SYSDATE is the server's date. If the server is physically located in a place that has a different timezone than the client, then these two will not be the same.

tyler_durden