Home » SQL & PL/SQL » SQL & PL/SQL » DATE Logic (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
icon6.gif  DATE Logic [message #332850] Wed, 09 July 2008 23:32 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi people ,

I have a table which contains data as follows:

v_session_id        DATE := trunc(SYSDATE);

desc adm.RECP_LIST;    

NAME                            Null?     Type
------------------------------- --------- -----
USERNAME                        NOT NULL  VARCHAR2(10)
RECP_TYPE                                 VARCHAR2(2)
TIME_STAMP                                DATE

select * from adm.RECP_LIST;

USERNAME   RECP_TYPE              TIME_STAMP
---------- ---------- ----------------------
BUDEVSBO   TO                      7/10/2008 
CABUSAME   TO                      7/10/2008 
CHEATHSF   TO                      7/10/2008 
MANSOJV    TO                      7/10/2008 
MORGANEA   TO                      7/10/2008 



When i try do a select like this, i get no entries, can someone please help ? Note v_session_id DATE := trunc(SYSDATE);

  select distinct a.username,b.email,a.recp_type
    from ADM.recp_list a,av_users b
        where a.username = b.rn
        and a.time_stamp = v_session_id
    order by username;


I am BAD at DATE

[Updated on: Wed, 09 July 2008 23:34]

Report message to a moderator

Re: DATE Logic [message #332855 is a reply to message #332850] Wed, 09 July 2008 23:36 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Try using TO_CHAR,TO_DATE,TRUNC functions.

In one table you have truncated time part and in other it's not

01/07/2007 00:00:00 is not equal to 01/07/2007 12:23:00.

Compare by truncating time part

Regards,
Rajat

[Updated on: Wed, 09 July 2008 23:37]

Report message to a moderator

Re: DATE Logic [message #332857 is a reply to message #332850] Wed, 09 July 2008 23:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select to_char(time_stamp, 'DD/MM/YYYY HH24:MI:SS') from ADM.recp_list;

And you will understand.

Regards
Michel
Re: DATE Logic [message #332861 is a reply to message #332857] Wed, 09 July 2008 23:50 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
oh my god !! Shocked

select to_char(time_stamp, 'DD/MM/YYYY HH24:MI:SS') from ADM.recp_list;

TO_CHAR(TIME_STAMP,'DD/MM/YYYYHH24:MI:SS')
------------------------------------------
10/07/2008 00:00:00                        
10/07/2008 00:00:00                        
10/07/2008 00:00:00                        
10/07/2008 00:00:00                        
10/07/2008 00:00:00   


But i have inserted the values into ADM.recp_list like shown below using v_session_id, where v_session_id = trunc(sysdate) so how can the above happen ?

                        insert into ADM.recp_list(username,recp_type,time_stamp)
                        values(v_user,'TO',v_session_id);  


Re: DATE Logic [message #332865 is a reply to message #332861] Wed, 09 July 2008 23:57 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
jagannathkiran wrote on Wed, 09 July 2008 23:50
Quote:

But i have inserted the values into ADM.recp_list like shown below using v_session_id, where v_session_id = trunc(sysdate) so how can the above happen ?


Because you have used trunc(sysdate) that truncates time part.

Regards,
Rajat


Re: DATE Logic [message #332886 is a reply to message #332850] Thu, 10 July 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure "a.username = b.rn" is true for something?

Regards
Michel
Re: DATE Logic [message #332892 is a reply to message #332886] Thu, 10 July 2008 01:00 Go to previous message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hey guys, I guess i need a whacking because there was a minor data logic hitch in my code, i apologize for the same, but yeah i did learn something good about TRUNC, thanks Michel and Rajat
Previous Topic: About Table Structure
Next Topic: Matrix like SQL
Goto Forum:
  


Current Time: Tue Dec 06 16:22:18 CST 2016

Total time taken to generate the page: 0.13510 seconds