Home » SQL & PL/SQL » SQL & PL/SQL » decode (to_date to_char., default) (merged)
decode (to_date to_char., default) (merged) [message #392333] Tue, 17 March 2009 08:56 Go to next message
kapusta117
Messages: 4
Registered: December 2008
Junior Member
Hello

I have a problem with dates in my sql query. In fact i have a form of research by date. This one can be either the Month entered under format YYYYMM or the date of the beginning under format (YYYYMMDD) and/or Completion date (YYYYMMDD: the latter is replaced by sysdate if not well informed). My problem is that when date of the beginning and/or the completion date are returned: j' have the Desired Result WELL. On the other hand when I return the date of month under formatat ' YYYYMM' I have NO result (whereas I should HAVE data returned)... Here my sql query :


Select t.rcv_no,c.dat_cde,t.date_exped,c.reference
FROM cde_commande_client c
,cde_commande_suivi_trsp t
where
c.COD_CDE=T.COD_CDE
AND
(
dat_cde>= DECODE('".$MOIS."','',to_date('".$DATE_DEBUT."','YYYYMMDD'),to_date('".$MOIS."','YYYYMM'))
AND
dat_cde<=
DECODE('".$MOIS."','',to_date(nvl('".$DATE_FIN."',to_char(sysdate,'YYYYMMDD')),'YYYYMMDD'),
to_char(sysdate,'YYYYMM') )

)
AND t.rcv_no=to_number(nvl('".$RCV."',trim(to_char(t.rcv_no))))

"


I thins problem is linker <ith month format in the default choice of Decode function , but i dont know how to verify it...
i tied to use to_date (to_char ...) but no result neitheir error message...

Please Help
Thank you
Best Regards
Re: decode (to_date to_char., default) [message #392335 is a reply to message #392333] Tue, 17 March 2009 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

do not cross/multi-post
Re: decode (to_date ) [message #392339 is a reply to message #392333] Tue, 17 March 2009 09:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You might want to replace
to_char(sysdate,'YYYYMM') 
with
trunc(sysdate,'MM')
as you are currently getting your datatypes confused, and there will be an implicit type conversion going on in there somewhere.
Re: decode (to_date ) [message #392340 is a reply to message #392339] Tue, 17 March 2009 09:44 Go to previous messageGo to next message
kapusta117
Messages: 4
Registered: December 2008
Junior Member
Hello

Thank you for replaying.

I have replaced to_char(sysdate,'YYYYMM') with
trunc(sysdate,'MM').. But NO RESULT ( i have no data returned)..
In fact date are stored in my Database Like this exemple :

13/03/2009 16:41:53 .
May be this could give an idea about format date!

Thank you for Help

Best Regards
Re: decode (to_date to_char., default) (merged) [message #392344 is a reply to message #392333] Tue, 17 March 2009 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Thu, 18 December 2008 14:18
...
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Re: decode (to_date ) [message #392346 is a reply to message #392340] Tue, 17 March 2009 10:06 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid there's too much we don't know for further speculation to be much help at this point.

Can you provide:
1) CREATE TABLE statements for the tables in question
2) A set of insert statements that will provide enough data to demonstrate the problem
3) Values for $DATE_DEBUT, $DATE_FIN and $MOIS, along with the results that you want to see from your test data, and the values that you are getting.
Previous Topic: Migration Script using a query
Next Topic: comma separated values
Goto Forum:
  


Current Time: Sun Dec 11 00:29:24 CST 2016

Total time taken to generate the page: 0.08129 seconds