Home » SQL & PL/SQL » SQL & PL/SQL » data type issue
data type issue [message #188631] Mon, 21 August 2006 01:45 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
I am driving a number in this format
DDDYHHMMSS
where
DDD = Julain date
U - Year Y =2nd digit of the year

HHMMSS =time

Thus i have written a function to drive
this like
FUNCTION get_transaction_number
    RETURN NUMBER IS
      --
      lv_transaction_number NUMBER;
      lv_julian_date 	  Number;
      lv_year               Number;
      lv_time_transaction   Number;
      BEGIN
        select to_char(sysdate,'ddd')  ,to_char(sysdate,'Y') ,
           to_char(sysdate,'HHMMSS') into
            lv_julian_date,lv_year,lv_time_transaction
            from dual;
    lv_transaction_number := lv_julian_date||lv_year||lv_time_transaction;--||lpad(lv_sequence_number,4,'0');
      RETURN (lv_transaction_number);
      EXCEPTION
        WHEN OTHERS THEN
         RAISE;
   END get_transaction_number;


My problem here is simple query return
select to_char(sysdate,'ddd') 
           ,to_char(sysdate,'Y') ,
            to_char(sysdate,'HHMMSS')
             from dual;

value
233,6,070806

But the actual function return
233670806

ie ignoring the zero value at position 1, I have tried to cast but none of them found ok ..
can anyone figure out what is best way to get this.
Re: data type issue [message #188644 is a reply to message #188631] Mon, 21 August 2006 02:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why do people get so confused about datatypes.
Dates go in DATE fields, numbers go in NUMBER fields and most everything else goes in VARCHAR2.

The solution to the problems you've reported, (as opposed to the soution to this terrible data construct that you're trying to create) is that the variable lv_time_transaction is defined as a number. So, you're selecting a char string into it, which forces an implicit type conversion to number .By definition, numbers do not store leading zeros. You then do another implicit type conversion back to a string when you concatenate it into the output.

Solutions:
lv_transaction_number := lv_julian_date||lv_year||to(char(lv_time_transaction,'000000');

or
lv_time_transaction   varchar2(6);


You will also be getting a problem like this when the julian date is < 100.

Why don't you just pass a DATE fiels around - it contains more information that you are passing, guarantees the data is valid, and you wouldn't get this sort of problem.
Re: data type issue [message #188652 is a reply to message #188631] Mon, 21 August 2006 02:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Use Fill Mode (FM).
TO_CHAR(sysdate, 'DDDYFMHH24FMMMSS')

The first FM toggles Fill Mode on, so that single digit hours display without a leading 0. The second one toggles it off again so that single digit minutes and seconds display with zeros.

I also used HH24, thinking you might want a 24 hour clock rather than 12.

Ross Leishman
Re: data type issue [message #188658 is a reply to message #188652] Mon, 21 August 2006 02:35 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ross? Aren't you on leave?

MHE
Re: data type issue [message #188659 is a reply to message #188652] Mon, 21 August 2006 02:37 Go to previous messageGo to next message
sanjit
Messages: 65
Registered: November 2001
Member
rleishman

could you please through some light on Fill Mode on on off.

i didnot understand what is meant?
thanks
Re: data type issue [message #188662 is a reply to message #188658] Mon, 21 August 2006 02:44 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Maaher wrote on Mon, 21 August 2006 17:35

Ross? Aren't you on leave?

MHE


What is "Leave" exactly? Am I at work? No.

Sanjit, just try various combinations of adding and removing the "FM" from the TO_CHAR expression above. You're clever, you'll work it out.

Ross Leishman
Previous Topic: insert into
Next Topic: Average getting calculated incorrectly using group by grouping sets
Goto Forum:
  


Current Time: Fri Dec 02 20:31:48 CST 2016

Total time taken to generate the page: 0.13872 seconds