Home » SQL & PL/SQL » SQL & PL/SQL » Date format
icon8.gif  Date format [message #294073] Wed, 16 January 2008 06:11 Go to next message
kekanap
Messages: 18
Registered: January 2008
Location: Centurion
Junior Member

Hallo Friends,

I need your help. I want the "The_Date" column to be in "DD-MM-YYYY HH24:MI:SS"

SELECT TO_DATE(A.THE_DATE, 'DD-MM-YYYY') AS THE_DATE, A.TOT_GENERATED_TICKETS, C.CANCELLED_TICKETS, B.FRAUD_TICKETS, D.TOT_CLOSED_TICKETS,
(B.FRAUD_TICKETS/D.TOT_CLOSED_TICKETS)*100 AS PERCENT_FRAUD FROM
(SELECT TO_CHAR(CREATION_DATE,'DD-MM-YYYY') AS THE_DATE, COUNT (1) AS TOT_GENERATED_TICKETS FROM
(SELECT * FROM CM_TICKET UNION SELECT * FROM CM_TICKET_ARCHIVE WHERE CASE_HISTORY_ID NOT IN
(SELECT CASE_HISTORY_ID FROM CM_TICKET)) WHERE CREATION_DATE > SYSDATE - 1
GROUP BY TO_CHAR(CREATION_DATE,'DD-MM-YYYY')) A,
(SELECT A.THE_DATE, COUNT(1) AS FRAUD_TICKETS FROM
(SELECT TO_CHAR(DATE_TIME, 'DD-MM-YYYY') AS THE_DATE, CASE_HISTORY_ID AS THE_CASE_HISTORY, MAX(DATE_TIME) AS DATE_CLOSED
FROM
(SELECT * FROM CM_ACTIVITIES UNION SELECT * FROM CM_ACTIVITIES_ARCHIVE WHERE CASE_HISTORY_ID NOT IN
(SELECT CASE_HISTORY_ID FROM CM_ACTIVITIES)) WHERE DATE_TIME > SYSDATE - 1 AND ACTION_ID = 15
GROUP BY TO_CHAR(DATE_TIME, 'DD-MM-YYYY'),CASE_HISTORY_ID) A,
(SELECT * FROM CM_TICKET_ARCHIVE WHERE CM_FRAUD_TYPE <> 'No Fraud') B
WHERE A.THE_CASE_HISTORY = B.CASE_HISTORY_ID GROUP BY A.THE_DATE) B,
(SELECT TO_CHAR(CANCELLED_DATE,'DD-MM-YYYY') AS THE_DATE, COUNT(1)
AS CANCELLED_TICKETS FROM CM_CANCELLED_TICKETS
WHERE CANCELLED_DATE > SYSDATE - 1 GROUP BY TO_CHAR(CANCELLED_DATE,'DD-MM-YYYY')) C,
(SELECT THE_DATE, COUNT(DATE_CLOSED) AS TOT_CLOSED_TICKETS
FROM
(SELECT TO_CHAR(DATE_TIME, 'DD-MM-YYYY') AS THE_DATE,MAX(DATE_TIME) AS DATE_CLOSED
FROM
(SELECT * FROM CM_ACTIVITIES UNION SELECT * FROM CM_ACTIVITIES_ARCHIVE WHERE CASE_HISTORY_ID NOT IN
(SELECT CASE_HISTORY_ID FROM CM_ACTIVITIES)) WHERE DATE_TIME > SYSDATE - 1 AND ACTION_ID = 15
GROUP BY TO_CHAR(DATE_TIME, 'DD-MM-YYYY'),CASE_HISTORY_ID) GROUP BY THE_DATE) D
WHERE A.THE_DATE = B.THE_DATE(+) AND A.THE_DATE = C.THE_DATE(+) AND A.THE_DATE = D.THE_DATE(+) ORDER BY THE_DATE;


This is the output below.

MINOTAUR DAILY TICKET GENERATION REPORT
========================================

| GENERATED| CANCELLED| FRAUD| CLOSED|PERCENT
THE_DATE | TICKETS| TICKETS| TICKETS| TICKETS| FRAUD
=========|==========|==========|==========|==========|=======
15-JAN-08| 3581| 1617| | |
16-JAN-08| 6300| 2084| | 1|
SQL>
Re: Date format [message #294078 is a reply to message #294073] Wed, 16 January 2008 06:21 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try TO_CHAR instead of TO_DATE
Re: Date format [message #294080 is a reply to message #294073] Wed, 16 January 2008 06:27 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hoping that The_Date is VARCHAR2 field Smile

Thumbs Up
Rajuvan

[Updated on: Wed, 16 January 2008 06:28]

Report message to a moderator

Re: Date format [message #294085 is a reply to message #294073] Wed, 16 January 2008 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Date format [message #294086 is a reply to message #294073] Wed, 16 January 2008 06:37 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member

SELECT   TO_DATE (a.the_date, 'DD-MM-YYYY') AS the_date,
         a.tot_generated_tickets, c.cancelled_tickets, b.fraud_tickets,
         d.tot_closed_tickets,
         (b.fraud_tickets / d.tot_closed_tickets) * 100 AS percent_fraud
    FROM (SELECT   TO_CHAR (creation_date, 'DD-MM-YYYY') AS the_date,
                   COUNT (1) AS tot_generated_tickets
              FROM (SELECT *
                      FROM cm_ticket
                    UNION
                    SELECT *
                      FROM cm_ticket_archive
                     WHERE case_history_id NOT IN (SELECT case_history_id
                                                     FROM cm_ticket))
             WHERE creation_date > SYSDATE - 1
          GROUP BY TO_CHAR (creation_date, 'DD-MM-YYYY')) a,
         (SELECT   a.the_date, COUNT (1) AS fraud_tickets
              FROM (SELECT   TO_CHAR (date_time, 'DD-MM-YYYY') AS the_date,
                             case_history_id AS the_case_history,
                             MAX (date_time) AS date_closed
                        FROM (SELECT *
                                FROM cm_activities
                              UNION
                              SELECT *
                                FROM cm_activities_archive
                               WHERE case_history_id NOT IN (
                                                        SELECT case_history_id
                                                          FROM cm_activities))
                       WHERE date_time > SYSDATE - 1 AND action_id = 15
                    GROUP BY TO_CHAR (date_time, 'DD-MM-YYYY'),
                             case_history_id) a,
                   (SELECT *
                      FROM cm_ticket_archive
                     WHERE cm_fraud_type <> 'No Fraud') b
             WHERE a.the_case_history = b.case_history_id
          GROUP BY a.the_date) b,
         (SELECT   TO_CHAR (cancelled_date, 'DD-MM-YYYY') AS the_date,
                   COUNT (1) AS cancelled_tickets
              FROM cm_cancelled_tickets
             WHERE cancelled_date > SYSDATE - 1
          GROUP BY TO_CHAR (cancelled_date, 'DD-MM-YYYY')) c,
         (SELECT   the_date, COUNT (date_closed) AS tot_closed_tickets
              FROM (SELECT   TO_CHAR (date_time, 'DD-MM-YYYY') AS the_date,
                             MAX (date_time) AS date_closed
                        FROM (SELECT *
                                FROM cm_activities
                              UNION
                              SELECT *
                                FROM cm_activities_archive
                               WHERE case_history_id NOT IN (
                                                        SELECT case_history_id
                                                          FROM cm_activities))
                       WHERE date_time > SYSDATE - 1 AND action_id = 15
                    GROUP BY TO_CHAR (date_time, 'DD-MM-YYYY'),
                             case_history_id)
          GROUP BY the_date) d
   WHERE a.the_date = b.the_date(+) AND a.the_date = c.the_date(+)
         AND a.the_date = d.the_date(+)
ORDER BY the_date;


Please format the code from next time.

This is my observation
SQL> alter session set nls_Date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.


SQL> select sysdate from dual;

SYSDATE
--------------------
16-jan-2008 12:34:54


SQL> select to_char(sysdate,'dd-mm-yyyy') from dual;

TO_CHAR(SYSDATE,'DD-MM-YYYY')
---------------------------------------------------------------------------
16-01-2008


SQL> select to_date(to_char(sysdate,'dd-mm-yyyy'),'dd-mm-yyyy') from dual;

TO_DATE(TO_CHAR(SYSD
--------------------
16-jan-2008 00:00:00

You are trimming of the time component from the date and converting it back to date in your final select. If this is what you want an alter session should be enough.

HTH

Regards

Raj
Re: Date format [message #294089 is a reply to message #294073] Wed, 16 January 2008 06:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

OP didn't want to set HH24:MM:SS to Zero .

He could directly use the format he meantioned along with TO_CHAR if the THE_DATE is date field .

Thumbs Up
rajuvan.

[Updated on: Wed, 16 January 2008 06:56]

Report message to a moderator

Re: Date format [message #294091 is a reply to message #294089] Wed, 16 January 2008 06:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

(SELECT TO_CHAR(CREATION_DATE,'DD-MM-YYYY') AS THE_DATE, COUNT (1) AS TOT_GENERATED_TICKETS FROM
(SELECT * FROM CM_TICKET UNION SELECT * FROM CM_TICKET_ARCHIVE WHERE CASE_HISTORY_ID NOT IN
(SELECT CASE_HISTORY_ID FROM CM_TICKET)) WHERE CREATION_DATE > SYSDATE - 1
GROUP BY TO_CHAR(CREATION_DATE,'DD-MM-YYYY')) A,

In the inner query if my inference is correct, creation_date got to be a date field because it is compared with sysdate. I know it is a very big assumption but going forward in the outer query OP is using to_char(creation_date,'dd-mm-yyyy'), so effectively OP is losing the time component from the date. This is what I am trying to highlight.

Regards

Raj
Re: Date format [message #294092 is a reply to message #294073] Wed, 16 January 2008 07:05 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Nice Observation anyway

Thumbs Up
Rajuvan
Previous Topic: Query Help - Group By or Other solution?
Next Topic: last day of quarter
Goto Forum:
  


Current Time: Sat Dec 10 10:53:41 CST 2016

Total time taken to generate the page: 0.04465 seconds