Home » SQL & PL/SQL » SQL & PL/SQL » query help
query help [message #200466] Tue, 31 October 2006 00:57 Go to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

dear experts, i'm practiceing Oracle.
i'm in a little trouble for the below two tasks for the emp table. can you please resolve the issue please?

1) Change the hire date of employee 200 to same date but time should be 10:30 a:m.

2) Replace the leading zeroes in employee id with *.

thank you very much.
Re: query help [message #200486 is a reply to message #200466] Tue, 31 October 2006 01:50 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select empno, hiredate from emp where empno = 7934;
 
     EMPNO HIREDATE
---------- --------------------
      7934 23-jan-1982 00:00:00

1 rows selected.

SQL> update emp
  2  set hiredate = to_date('23-jan-1982 10:30:00','dd-mon-yyyy hh24:mi:ss')
  3  where empno = 7934;

1 row updated.

SQL> select empno, hiredate from emp where empno = 7934;

     EMPNO HIREDATE
---------- --------------------
      7934 23-jan-1982 10:30:00


hope this helps.
Re: query help [message #200487 is a reply to message #200486] Tue, 31 October 2006 01:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A solution that didn't require you to know what the hiredate actually was in advace would be this:
SQL> update emp
  2  set hiredate = trunc(hiredate)+(10.5/24)
  3  where empno = 7934;

The 10.5/24 is simply the fraction of a day that the time 10:30 AM corresponds to.
Re: query help [message #200488 is a reply to message #200466] Tue, 31 October 2006 01:55 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
can you try using following functions?

select to_char(trunc(sysdate) + 10/24 + 30/(24*60),'dd-mon-yyyy hh24:mi:ss') from dual;

select lpad(ltrim('0001202','0'),length('0001202'),'*') from dual;


By
Vamsi
Re: query help [message #200521 is a reply to message #200466] Tue, 31 October 2006 04:19 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

Dear friends,
how can i work with dual table while i need it in Emp? can you guide me

[Updated on: Tue, 31 October 2006 04:21]

Report message to a moderator

Re: query help [message #200522 is a reply to message #200521] Tue, 31 October 2006 04:27 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Hi,
I have given examples only. Not the exact statements.
I think you can get your required solution using the functions which I have used.

Try that out yourself. Let us know if you really fail to do that.

By
Vamsi
Previous Topic: using Type in In list
Next Topic: How do I check for a number within a string of numbers?
Goto Forum:
  


Current Time: Sun Dec 11 06:19:57 CST 2016

Total time taken to generate the page: 0.09792 seconds