Login or Register to Ask a Question and Join Our Community


Programming

Trouble with Date Variables and Functions in PL/SQL

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Trouble with Date Variables and Functions in PL/SQL
# 1  
Old 08-25-2008
Error Trouble with Date Variables and Functions in PL/SQL
Hi,
In the course of my script i have to compare SYSDATE with the 15th of the current month:
if it is greater than i should set a variable date to 15th of the next month
if less than i should set it to the 15th of the current month.

In other words the question is how to set a date variable automatically in accordance with SYSDATE.

Anyone has an idea?
Thanks in advance. Smilie
# 2  
Old 08-25-2008
you mean you are taking SYSDATE from oracle and comparing it with unix date??
# 3  
Old 08-25-2008
Code:
SET SERVEROUTPUT ON SIZE 10000
DECLARE
FUNCTION GIVE_ME_A_DATE
         RETURN DATE 
IS         
    DAY VARCHAR(4):=TO_CHAR(SYSDATE, 'DD');
    MONTH VARCHAR2(4):=TO_CHAR(SYSDATE,'MON');
    YEAR VARCHAR2(5):=TO_CHAR(SYSDATE,'YYYY');
    CORRECT DATE:=TO_DATE(YEAR||'-'|| MONTH ||'-'||'15', 'YYYY-MON-DD');
BEGIN
	IF TO_NUMBER(DAY,'99') > 15
	THEN
                 CORRECT:=ADD_MONTHS(CORRECT,1);
	END IF;
             RETURN CORRECT;
END;


BEGIN 
    DBMS_OUTPUT.ENABLE(10000);
    
    DBMS_OUTPUT.PUT_LINE('TODAY is ' || 
                 TO_CHAR(SYSDATE, 'DD-MON-YYYY'));
    DBMS_OUTPUT.PUT_LINE('Corrected date is: '|| 
               TO_CHAR(GIVE_ME_A_DATE, 'DD-MON-YYYY'));
END;
/

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

ksh While Loop - passing variables to functions

I'm reading a text file using a while loop but when I call a function from within this loop it exits that same iteration … even though there are many more lines in the file to be read. I thought it was something to do with the IFS setting but it appears that a function call (when run... (3 Replies)
Discussion started by: user052009
3 Replies

2. Shell Programming and Scripting

functions and variables in bash

I have a bash script with some functions as below and am wondering if I can use the variables declared in setup in the other functions and in the rest of the bash script. setup(){ none=0; low=1; medium=2; high=3; debug=4 var1="red" var2="fred" } create_basemap() { ... (7 Replies)
Discussion started by: kristinu
7 Replies

3. Shell Programming and Scripting

variables and functions

can somebody telll me what my values are not being displayed in the function func1() {oracle@im4s012nz1_DUMMY}$ cat x1.ksh #!/bin/ksh getpwd() ( set -x . /home/oracle/dba/bin/CyberArk/CyberArk_GetPass.ksh ORA_USER=$DB_UID ORA_PWD=$DB_PWD echo "Here I am 1... (6 Replies)
Discussion started by: BeefStu
6 Replies

4. UNIX for Dummies Questions & Answers

Trouble Assigning AWK variables

Hi, I made an executable file in terminal and it looks like this. echo Enter the name of the file without the .wig extension read NAME echo Enter the ratio read RATIO awk '{$2*=$RATIO;{print $0}}' ${NAME}.wig > ${NAME}normalized.wig I have a file with several million lines that look... (6 Replies)
Discussion started by: wyarosh
6 Replies

5. Programming

SQL Functions - PostgreSQL

Hi guys. I have some questions about SQL functions in postgresql: 1. Can a SQL function call another SQL function? 2. How about recursive calls? 3. Consider we have function that has a varchar argument. CREATE FUNCTION func(varchar) RETURN void AS $$ some SQL queries. ... (0 Replies)
Discussion started by: majid.merkava
0 Replies

6. Shell Programming and Scripting

unix variables from sql / pl/sql

How do I dynamically assign the below output to unix shell variables so I can build a menu in a shell script? Example: var1 = 1 var2= SYSTEM var3 = 2 var4= UNDOTBS1 and so on, then in the shell script I can use the variables to build a menu. set serveroutput on declare... (2 Replies)
Discussion started by: djehres
2 Replies

7. Shell Programming and Scripting

[bash] reassigning referenced variables in functions

Hello all, Problem. ---------- I'm trying to reassign a referenced variable passed to a 'local' variable in a function but the local variable refuses to be assigned the content of the referenced variable. Any ideas would be appreciated. Objective. ----------- Eliminate all $VAR... (1 Reply)
Discussion started by: ASGR
1 Replies

8. Shell Programming and Scripting

perl: storing regex in array variables trouble

hi this is an example of code: use strict; use warnings; open FILE, "/tmp/result_2"; my $regex="\\ Starting program ver. (.*)"; my $res="Program started, version <$1> - OK.\n"; while (<FILE>) { if ($_ =~ /($regex)/) { print "$res"; } } close FILE; This finds $regex and print... (3 Replies)
Discussion started by: xist
3 Replies

9. Shell Programming and Scripting

awk - arithemetic functions with external variables

I'm trying to get awk to do arithmetic functions with external variables and I'm getting an error that I cannot figure out how to fix. Insight would be appreciated money=$1 rate1=$(awk -F"\t " '/'$converting'/{print $3}' convert.table) rate2=$(awk -F"\t"... (2 Replies)
Discussion started by: DKNUCKLES
2 Replies

10. Shell Programming and Scripting

Variables and functions

I have a script that ultimately will FTP data to certain directories based on a character in the filename. I am creating a function within my script to handle the FTP call. Are the variables that are setup in the original script accessible to the function? If not, is there a way to allow them to... (4 Replies)
Discussion started by: dfb500
4 Replies
Login or Register to Ask a Question