Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to use sql "sysdate" in unix?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to use sql "sysdate" in unix?
# 1  
Old 10-21-2011
Tools How to use sql "sysdate" in unix?
I am use unix shell script to called an sql Script to query data in my shell program.
Code:
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

Code:
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

Last edited by Franklin52; 10-21-2011 at 07:51 AM.. Reason: Please use code tags, thank you
# 2  
Old 10-21-2011
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  
Old 10-21-2011
Some more questions/comments:
-----> What is the type of "insert_date" column in table: table_X
Code:
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!
Last edited by radoulov; 10-21-2011 at 08:35 AM.. Reason: Code tags added.
# 4  
Old 10-21-2011
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:
Code:
update table_X set x_flag = 'complete'
where x.insert_date = sysdate;

See sysdate for more information.
# 5  
Old 10-21-2011
Quote:
Originally Posted by cero
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.

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

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

Quote:
Originally Posted by felipe.vinturin
Some more questions/comments:
-----> What is the type of "insert_date" column in table: table_X
Code:
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!

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

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

Quote:
Originally Posted by m.d.ludwig
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:
Code:
update table_X set x_flag = 'complete'
where x.insert_date = sysdate;

See sysdate for more information.
Thank a lot for information ... my data type is date , then i use trunc(sysdate) it work.
# 6  
Old 10-21-2011
Quote:
Originally Posted by krai
...Sysdate in Unix not use Format Date then i use "trunc(sysdate)" ^_^
...
Actually, the Unix date can be formatted and even passed as a parameter to an Oracle script.

Code:
$
$ 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
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Bash script - Print an ascii file using specific font "Latin Modern Mono 12" "regular" "9"

Hello. System : opensuse leap 42.3 I have a bash script that build a text file. I would like the last command doing : print_cmd -o page-left=43 -o page-right=22 -o page-top=28 -o page-bottom=43 -o font=LatinModernMono12:regular:9 some_file.txt where : print_cmd ::= some printing... (1 Reply)
Discussion started by: jcdole
1 Replies

2. UNIX for Dummies Questions & Answers

Using "mailx" command to read "to" and "cc" email addreses from input file

How to use "mailx" command to do e-mail reading the input file containing email address, where column 1 has name and column 2 containing “To” e-mail address and column 3 contains “cc” e-mail address to include with same email. Sample input file, email.txt Below is an sample code where... (2 Replies)
Discussion started by: asjaiswal
2 Replies

3. UNIX for Dummies Questions & Answers

Unix "look" Command "File too large" Error Message

I am trying to find lines in a text file larger than 3 Gb that start with a given string. My command looks like this: $ look "string" "/home/patrick/filename.txt" However, this gives me the following message: "look: /home/patrick/filename.txt: File too large" So, I have two... (14 Replies)
Discussion started by: shishong
14 Replies

4. Shell Programming and Scripting

awk command to replace ";" with "|" and ""|" at diferent places in line of file

Hi, I have line in input file as below: 3G_CENTRAL;INDONESIA_(M)_TELKOMSEL;SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL My expected output for line in the file must be : "1-Radon1-cMOC_deg"|"LDIndex"|"3G_CENTRAL|INDONESIA_(M)_TELKOMSEL"|LAST|"SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL" Can someone... (7 Replies)
Discussion started by: shis100
7 Replies

5. Shell Programming and Scripting

Pass parameter from PL/SQL to Unix "as is"

Hi, I need to pass 4 parameters from Oracle Procedure to Unix Shell script.. procedure signature :- UNIX Shell script: Problem: 1. Suppose pdffile and pdfresult has Null values in Oracle procedures but unix shell will read parameters by spaces so I am getting wrong parameters... (7 Replies)
Discussion started by: sandy162
7 Replies

6. Shell Programming and Scripting

Unix commands delete all files starting with "X" except "X" itself. HELP!!!!?

im a new student in programming and im stuck on this question so please please HELP ME. thanks. the question is this: enter a command to delete all files that have filenames starting with labtest, except labtest itself (delete all files startign with 'labtest' followed by one or more... (2 Replies)
Discussion started by: soccerball
2 Replies

7. UNIX for Dummies Questions & Answers

Explain the line "mn_code=`env|grep "..mn"|awk -F"=" '{print $2}'`"

Hi Friends, Can any of you explain me about the below line of code? mn_code=`env|grep "..mn"|awk -F"=" '{print $2}'` Im not able to understand, what exactly it is doing :confused: Any help would be useful for me. Lokesha (4 Replies)
Discussion started by: Lokesha
4 Replies

8. UNIX for Advanced & Expert Users

Commands on Digital Unix equivalent to for "top" and "sar" on other Unix flavour

Hi, We have a DEC Alpha 4100 Server with OSF1 Digital Unix 4.0. Can any one tell me, if there are any commands on this Unix which are equivalent to "top" and "sar" on HP-UX or Sun Solaris ? I am particularly interested in knowing the CPU Load, what process is running on which CPU, etc. ... (1 Reply)
Discussion started by: sameerdes
1 Replies
Login or Register to Ask a Question