Using decode with dates

[Jyothi Kavasseri]

The decode always returns the date format in DD-MMM-YY format whereas I want it to return in MM/DD/YYYY format.

select decode(to_date(to_char(SYSDATE,'YYYYMM'),'YYYYMM'),'000000',null,to_date(to_char(SYSDATE,'YYYYMM'),'YYYYMM')) from dual.

As of now, when I type this I do not have a proper SQL editor to test the syntax. Hence it may not be correct. However, I could run the decode query in TOAD at my work and that is when I saw it is returning in  DD-MMM-YY format. I hope you got some idea as to what my requirement is. Looking forward to your help.


Thanks in advance.

[Michael Moore]

since it will never be '000000' for any DATE, you can simply do...

SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY')  FROM DUAL;

Mike

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[Jyothi Kavasseri]

Unfortunately, we get '000000' at times.

[Michael Moore]

If you are getting '000000' then your example is not showing what you are actually doing. 

you can not make a DATE have a value of zeros.

SQL> select to_date('20110809','yyyymmdd') from dual

TO_DATE('20110809','YYYYMMDD')

------------------------------

09-AUG-11                     

1 row selected.

SQL> select to_date('00000000','yyyymmdd') from dual

select to_date('00000000','yyyymmdd') from dual

               *

Error at line 1

ORA-01843: not a valid month

[Jyothi Kavasseri]

This is why I use decode to change the zeros to null. My sql works except it is returning the valid dates in the wrong format. Like I said, the syntax here might be wrong since no database is accessible for me right now to test run my sql. Anyway, I will wait until tomorrow to get the right syntax and post it again. Thanks for your help.

[JK]

I tested it and this is the correct syntax and it returns 01-AUG-11.

SQL> select

decode(to_date(to_char(SYSDATE,'YYYYMM'),'YYYYMM'),'000000',null,to_date(to_char(SYSDATE

,'YYYYMM'),'YYYYMM')) from dual;

DECODE(TO
---------
01-AUG-11

SQL>

On Aug 11, 9:25 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
> If you are getting '000000' then your example is not showing what you are
> actually doing.
> you can not make a DATE have a value of zeros.
>
> SQL> select to_date('20110809','yyyymmdd') from dual
>
> TO_DATE('20110809','YYYYMMDD')
> ------------------------------
> 09-AUG-11
> 1 row selected.
>
> SQL> select to_date('00000000','yyyymmdd') from dual
> select to_date('00000000','yyyymmdd') from dual
>                *
> Error at line 1
> ORA-01843: not a valid month
>
>
>
> On Thu, Aug 11, 2011 at 6:05 PM, Jyothi Kavasseri <jyka...@gmail.com> wrote:
> > Unfortunately, we get '000000' at times.
>

> > On Thu, Aug 11, 2011 at 9:03 PM, Michael Moore <michaeljmo...@gmail.com>wrote:
>
> >> since it will never be '000000' for any DATE, you can simply do...
>
> >> SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY')  FROM DUAL;
> >> Mike
>

> >> On Thu, Aug 11, 2011 at 5:45 PM, Jyothi Kavasseri <jyka...@gmail.com>wrote:
>
> >>> The decode always returns the date format in DD-MMM-YY format whereas I
> >>> want it to return in MM/DD/YYYY format.
>
> >>> select

> >>> decode(to_date(to_char(SYSDATE,'YYYYMM'),'YYYYMM'),'000000',null,to_date(to­_char(SYSDATE,'YYYYMM'),'YYYYMM'))

> >http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text -
>
> - Show quoted text -

[JK]

I am just using SYSDATE as an example only since SYSDATE can never be
'000000'. In my case I have the following syntax to retrieve from a
table:


SQL> select decode(bill_begin_date,'000000',null,TO_DATE
(bill_begin_date, 'YYYYMM')) from TEMP_DETERMINATION;

DECODE(BI
---------







01-JUL-10
01-AUG-10



DECODE(BI
---------




01-AUG-10
01-SEP-10

On Aug 11, 9:25 pm, Michael Moore <michaeljmo...@gmail.com> wrote:

> If you are getting '000000' then your example is not showing what you are
> actually doing.
> you can not make a DATE have a value of zeros.
>
> SQL> select to_date('20110809','yyyymmdd') from dual
>
> TO_DATE('20110809','YYYYMMDD')
> ------------------------------
> 09-AUG-11
> 1 row selected.
>
> SQL> select to_date('00000000','yyyymmdd') from dual
> select to_date('00000000','yyyymmdd') from dual
>                *
> Error at line 1
> ORA-01843: not a valid month
>
>
>
> On Thu, Aug 11, 2011 at 6:05 PM, Jyothi Kavasseri <jyka...@gmail.com> wrote:
> > Unfortunately, we get '000000' at times.
>

> > On Thu, Aug 11, 2011 at 9:03 PM, Michael Moore <michaeljmo...@gmail.com>wrote:
>
> >> since it will never be '000000' for any DATE, you can simply do...
>
> >> SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY')  FROM DUAL;
> >> Mike
>

> >> On Thu, Aug 11, 2011 at 5:45 PM, Jyothi Kavasseri <jyka...@gmail.com>wrote:
>
> >>> The decode always returns the date format in DD-MMM-YY format whereas I
> >>> want it to return in MM/DD/YYYY format.
>
> >>> select

> >>> decode(to_date(to_char(SYSDATE,'YYYYMM'),'YYYYMM'),'000000',null,to_date(to­_char(SYSDATE,'YYYYMM'),'YYYYMM'))

[JK]

This problem is resolved.

On loading into the actual table with the DATE type column resolves
the format issue.

Thanks.

> > >http://groups.google.com/group/Oracle-PLSQL?hl=en-Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -

[Michael Moore]

Hi Jyothi,

Yes, a variable, (or table column) with a datatype of DATE can not be zeros. 

The key concept here is DATATYPE, not 'type', not 'data type', but 'datatype'. 

These are all different things. 

regards,

Mike

[Sagar Thakkar]

HI can some explain the case function below specially the DECODE (SIGN (TO_DATE (:P_CHK_RECD_TO, 'DD/MM/RRRR') - (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A') = 'A'

WHERE     CASE

                    WHEN Y.DOC_STATUS = 'R' AND DECODE (SIGN (TO_DATE (:P_CHK_RECD_TO, 'DD/MM/RRRR') - (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A') = 'A'

                    THEN

                       'A'

                    WHEN Y.DOC_STATUS = 'A'

                    THEN

                       'A'

                    ELSE

                       'R'

                 END = 'A'

[Michael Moore]

As far as I can tell:

1) this does not belong in the WHERE clause

2) the entire DECODE function could simply be replaced by (date1 > date2)

Consider the following example:

In case you don't know, DUAL is a table that has one column and one row. The column's name is "dummy" and the value of the row is "X".

SQL> select dummy from dual

DUMMY

-----

X    

1 row selected.

SQL> SELECT CASE WHEN dummy = 'X' AND SYSDATE + 1 > SYSDATE 

       THEN 'A' 

       WHEN dummy = 'X' 

       THEN 'B' 

       ELSE 'R' END result

FROM dual

RESULT

------

A     

1 row selected.

SQL> SELECT CASE WHEN dummy = 'X' AND SYSDATE > SYSDATE 

       THEN 'A' 

       WHEN dummy = 'X' 

       THEN 'B' 

       ELSE 'R' END result

FROM dual

RESULT

------

B     

1 row selected.

--

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
 

---

You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group.

To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[ddf]

On Monday, February 24, 2014 7:59:00 AM UTC-7, Sagar Thakkar wrote:

HI can some explain the case function below specially the DECODE (SIGN (TO_DATE (:P_CHK_RECD_TO, 'DD/MM/RRRR') - (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A') = 'A'

WHERE     CASE

                    WHEN Y.DOC_STATUS = 'R' AND DECODE (SIGN (TO_DATE (:P_CHK_RECD_TO, 'DD/MM/RRRR') - (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A') = 'A'

                    THEN

                       'A'

                    WHEN Y.DOC_STATUS = 'A'

                    THEN

                       'A'

                    ELSE

                       'R'

                 END = 'A'

Michael is correct, that does not belong in a WHERE clause.  But, that doesn't explain the DECODE statement, so let's take it apart.

The SIGN function returns one of three values: 1,0,-1.  Which value it returns depends on whether the value is positive (1), zero (0) or negative (-1):

SQL> select SIGN (TO_DATE ('&pdate', 'DD/MM/RRRR') - (TO_DATE('10/02/2014','DD/MM/RRRR'))) from dual;
Enter value for pdate: 01/02/2012

SIGN(TO_DATE('01/02/2012','DD/MM/RRRR')-(TO_DATE('10/02/2014','DD/MM/RRRR')))
-----------------------------------------------------------------------------
                                                                           -1

SQL> /
Enter value for pdate: 10/02/2014

SIGN(TO_DATE('10/02/2014','DD/MM/RRRR')-(TO_DATE('10/02/2014','DD/MM/RRRR')))
-----------------------------------------------------------------------------
                                                                            0

SQL> /
Enter value for pdate: 10/04/2014

SIGN(TO_DATE('10/04/2014','DD/MM/RRRR')-(TO_DATE('10/02/2014','DD/MM/RRRR')))
-----------------------------------------------------------------------------
                                                                            1

SQL>

Unfortunately the DECODE only handles the positive result:

SQL> select DECODE (SIGN (TO_DATE ('&pdate', 'DD/MM/RRRR') - (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A') from dual;
Enter value for pdate: 01/02/2012

D
-

SQL> /
Enter value for pdate: 10/02/2014

D
-

SQL> /
Enter value for pdate: 10/04/2014

D
-
A

SQL>

Not really a good strategy.  A default value should also be specified to handle the 'unhandled' cases:

SQL> select DECODE (SIGN (TO_DATE ('&pdate', 'DD/MM/RRRR') - (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A', 'X') from dual;
Enter value for pdate: 01/02/2012

D
-
X

SQL> /
Enter value for pdate: 10/02/2014

D
-
X

SQL> /
Enter value for pdate: 10/04/2014

D
-
A

SQL>

Ideally all three conditions should be explicitly handled with the default covering the unfortunate event when none of the expected values are returned:

SQL> select DECODE (SIGN (TO_DATE ('&pdate', 'DD/MM/RRRR') - (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A', 0, 'B', -1, 'X', 'Z') from dual;
Enter value for pdate: 01/02/2013

D
-
X

SQL> /
Enter value for pdate: 10/02/2014

D
-
B

SQL> /
Enter value for pdate: 10/04/2014

D
-
A

SQL>

Even using dates in the BC range won't return 'Z':

SQL>  select DECODE (SIGN (TO_DATE ('&pdate', 'DD/MM/SYYYY') - (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A', 0, 'B', -1, 'X', 'Z') from dual

   2>  /
Enter value for pdate: 01/01/-4712

D
-
X

SQL>

However all cases are now considered which is MUCH better programming practice.

Possibly you understand the DECODE and SIGN functions now.

David Fitzjarrell

[Michael Moore]

Amending what I said earlier, having this in the WHERE clause is not a problem.

WHERE     CASE

                    WHEN Y.DOC_STATUS = 'R' AND DECODE (SIGN (TO_DATE (:P_CHK_RECD_TO, 'DD/MM/RRRR') - (TO_DATE('10/02/2014','DD/MM/RRRR'))), 1, 'A') = 'A'

                    THEN

                       'A'

                    WHEN Y.DOC_STATUS = 'A'

                    THEN

                       'A'

                    ELSE

                       'R'

                 END = 'A'

The intent is:

Select the record if the doc_status is 'R' and Date1 > Date2

Select the record if the doc_status is 'A'

Of course and easier way to write this would be:

where (doc_status = 'R' and Date1>Date2)

          OR doc_status = 'A';

Mike

[Ashish Barot]

In this condition, if doc status is "R" and parameter date minus 10feb2014 is positive figure then doc status will be considered as authorised "A"

Regards,
Ashish

--