If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > How to check for data format in pl/sql

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 41
How to check for data format in pl/sql

Hi All,

I want to find out the date format.
say i have some date parameter for a procedure,
i want to ensure date value passed is of format 'YYYY-MM-DD'.
if not in the above format i have to return message date of improper format from my procedure.

can anyone help me how to achieve this.

Thanks & Regards in Advance
__________________
'A candle will loose nothing by lighting an another candle'
Reply With Quote
  #2 (permalink)  
Old
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Well, if the parameter passed is of type DATE (which seems appropriate), there is no format to check. Formats only apply to VARCHAR2 strings that are being converted to/from DATEs.

If the parameter is actually VARCHAR2 for some reason, then the easiest way to check the format matches what you expect is to "suck it and see":
Code:
PROCEDURE myproc (p_datestr IN VARCHAR2)
IS
  v_date DATE;
BEGIN
  -- Check the date format
  BEGIN
    v_date := TO_DATE(p_datestr,'YYYY-MM-DD');
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001,'Date string not in correct format');
  END;
  ...
END;
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Posts: 246
You really should pass it as a date, and have your calling software convert it from char to date (if needed) because "2004-05-01" is valid as both may 1st and jan 5th.
__________________
Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 41
Thank you very much for the replies, but i think i am not clear with the question,
now with this procedure i will explain my requirement.

PROCEDURE myproc (p_datestr IN DATE)
IS
v_date DATE;
BEGIN
-- Check the date format
BEGIN
v_date := TO_DATE(p_datestr,'YYYY-MM-DD');
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Date string not in correct format');
END;
...
END;

now in the above procedure parameter p_datestr is of data type date,
what i want to do is i want to confirm whether the p_datestr of of format
'YYYY-MM-DD'.

example: if parameter p-datestr is passed with value '27-10-2004',
but i want to check whether the format of p_datestr is of
format 'YYYY-MM-DD', since it not of the format i want to return
some error message.

what ever the solution given previously is to check whether the value of some date format.
my doubt is how can iassure date is of a particular format.


help will be highly appreciated.
thanks
__________________
'A candle will loose nothing by lighting an another candle'
Reply With Quote
  #5 (permalink)  
Old
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
But it is meaningless to "check the format" of a DATE parameter, because a DATE parameter is "unformatted". See what I mean:
Code:
SQL> create or replace procedure p (p_date in date) is
  2  begin
  3    dbms_output.put_line('Date is...      '||to_char(p_date,'DD-MON-YYYY'));
  4    dbms_output.put_line('Date is also... '||to_char(p_date,'YYYY-MM-DD'));
  5    dbms_output.put_line('As well as...   '||to_char(p_date,'YYYY-DD-MM'));
  6    dbms_output.put_line('Or perhaps...   '||to_char(p_date,'DD MONTH YYYY HH24:MI:SS'));
  7  end;
  8  /

Procedure created.

SQL> exec p(sysdate);
Date is...      27-OCT-2004
Date is also... 2004-10-27
As well as...   2004-27-10
Or perhaps...   27 OCTOBER   2004 13:07:08

PL/SQL procedure successfully completed.
By the time your procedure receives the DATE parameter, the calling program has already done any necessary conversion from formatted text to DATE, and the DATE is sure to be valid.

Further examples of calls to that procedure. The first uses the "default" format mask, the second specifies a different one.
Code:
SQL> exec p('01-jan-1979')
Date is...      01-JAN-1979
Date is also... 1979-01-01
As well as...   1979-01-01
Or perhaps...   01 JANUARY   1979 00:00:00

PL/SQL procedure successfully completed.

SQL> exec p (to_date('1984-12-25','YYYY-MM-DD'))
Date is...      25-DEC-1984
Date is also... 1984-12-25
As well as...   1984-25-12
Or perhaps...   25 DECEMBER  1984 00:00:00

PL/SQL procedure successfully completed.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews

Last edited by andrewst; 10-27-04 at 08:14.
Reply With Quote
  #6 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,393
I have seen this misconception over and over during the years. A date field has no format, it is a 7 character binary value that stores a timestamp down to an accruracy of one second. Using to_char the date field can be displayed in a number of ways, but the underlaying date column or field never changes. Just like if you have a number field containing the value 123456.78

You could display the field as
123456.78
123457 (rounded)
$123,456.78 (currency)

But the actual number never changes.
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.

Last edited by beilstwh; 10-27-04 at 09:31.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Posts: 1
date format

hello

there is nothing like a date format.

any date datatype is internally stored as a 7 byte data on Oracle
database.

the format only comes into picture when the parameter assigned to a date
variable is of varchar2 type.
other instances are where you need to print the date in a specific function
in which you would first convert it into a varchar2 and then print it.

if u are so keen on checking the format of the input parameter,pass it
as a varchar2 rather than a date.

regards
d'evil
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On