How can i change the date format of a particular date?

[v2k4u]

How can i change the date format of a particular date which is in the format for Sep,1,2012 12:00:00 AM into something like Sun 9/1 in the Report?

[RubenvdLinden]

Set the Format property to Date, Pattern: E M/d

[v2k4u]

Can you let me know how we can do the change in a Crosstab Report. Since even though i have applied the Data format property to E M/d there is no change that i can see.. Is it because of the Prompt page and filter that i have been using?

I have been using DT from the Native Sql as follows:

SELECT TO_DATE(TO_CHAR(a.row_date,'mm/dd/yyyy'),'mm/dd/yyyy') AS dt
        , CASE WHEN a.split = 42129 THEN 'Direct Check Enhancement' ELSE c.description END category
        , c.inbound_ind
        , SUM(a.acdcalls) + SUM(a.abandoned) AS offered
        , SUM(a.abandoned) AS abandoned
        , ROUND(SUM(a.acdtime+a.acwtime)/NULLIF(SUM(a.acdcalls),0),0) AS handle_time
        , SUM(a.calls_out) AS calls_out
        , ROUND(SUM(a.out_time)/NULLIF(SUM(a.calls_out),0),0) AS aht_out
        , SUM(availtime) AS availtime
        , SUM(stafftime) AS stafftime
        , SUM(auxtime) AS auxtime
        , NULL AS ans_time
        , SUM(a.acdcalls) AS acdcalls
        , SUM(a.acceptable) AS acceptable
        , ROUND(SUM(a.anstime)/NULLIF(SUM(a.acdcalls),0),0) AS asa_time
        , TO_NUMBER(TO_CHAR(b.date_key,'mm')) AS month
        , CASE WHEN b.date_key BETWEEN LAST_DAY(ADD_MONTHS(SYSDATE-1,-1)) AND LAST_DAY(SYSDATE-1) THEN 1 ELSE 0 END AS current_month
        , d.yyyymm AS yyyymm_weekly
        , CASE WHEN a.split = 42129 THEN 'TAMPA' ELSE c.site END site
        , d.week_nbr AS week_nbr_weekly
FROM (     SELECT 'SPLIT' AS source
                , site
                , row_date
                , split
                , abncalls AS abandoned
                , CASE WHEN site = 'Fannin' THEN outflowcalls
                            ELSE acdcalls END AS acdcalls
                , acwincalls AS ext_call_in
                , acwoutcalls + auxoutcalls AS ext_call_out
                , acwoutcalls AS acwoutcalls
                , auxoutcalls AS auxoutcalls
                , holdtime/60 AS holdtime
                , holdcalls AS holdcalls
                , transferred AS transout
                , acd
                , acdtime
                , acwtime
                , anstime
                , acwoutcalls + auxoutcalls AS calls_out
                , auxouttime + acwouttime AS out_time
                , i_availtime AS availtime
                , i_stafftime AS stafftime
                , i_auxtime AS auxtime
                , acceptable
            FROM mis.mis_local_cms_dsplit
            UNION ALL

            SELECT 'VDN' AS source
                , site
                , row_date
                , TO_NUMBER(vdn) AS split
                , abncalls AS abandoned
                , CASE WHEN site = 'Fannin' THEN outflowcalls
                            ELSE acdcalls END AS acdcalls
                , NULL AS ext_call_in
                , NULL AS ext_call_out
                , NULL AS acwoutcalls
                , NULL AS auxoutcalls
                , NULL AS holdtime
                , NULL AS holdcalls
                , NULL AS transout
                , 2 AS acd
                , acdtime
                , acwtime
                , NULL AS anstime
                , NULL AS calls_out
                , NULL AS out_time
                , NULL AS availtime
                , NULL AS stafftime
                , NULL AS auxtime
                , acceptable
            FROM mis.mis_local_cms_dvdn
        WHERE vdn <> 42129
        UNION
        SELECT 'VDN' AS source
                , a.site
                , row_date
                , TO_NUMBER(a.vdn) AS split
                , abncalls AS abandoned
                , acdcalls
                , NULL AS ext_call_in
                , NULL AS ext_call_out
                , NULL AS acwoutcalls
                , NULL AS auxoutcalls
                , NULL AS holdtime
                , NULL AS holdcalls
                , NULL AS transout
                , acd
                , NULL acdtime
                , NULL acwtime
                , NULL anstime
                , NULL AS calls_out
                , NULL AS out_time
                , NULL AS availtime
                , NULL AS stafftime
                , NULL AS auxtime
                , acceptable
            FROM  mis.mis_local_cms_dvdn a
        WHERE site =  'Tampa' and vdn = 42129 and (skill1=231 OR skill2=231 OR skill3=231)
            ) a
JOIN dms.date_table b ON a.row_date = b.date_key 
LEFT JOIN mis_stage.acd_reporting_lookup c
    ON (UPPER(a.site) = c.site
        AND a.split = c.split
        AND a.row_date BETWEEN c.date_active_from AND c.date_active_to
        AND a.acd = NVL(c.acd,a.acd)
        AND a.source = c.type
        )
JOIN (        SELECT
        a.date_key
        ,CASE   WHEN NVL(b.rank,5) = 1 and CASE   WHEN b.rank = 1 then b.dte-TRUNC(a.date_key,'MONTH') + 1
                WHEN b.rank IN (2,3,4,5) THEN 7
                ELSE LAST_DAY(a.date_key) - MAX(b.dte) OVER (PARTITION BY TO_CHAR(a.date_key,'YYYYMM'))
        END <=3 THEN TO_CHAR(ADD_MONTHS(date_key,-1),'yyyymm')
                WHEN NVL(b.rank,5) = 5 AND CASE   WHEN b.rank = 1 then b.dte-TRUNC(a.date_key,'MONTH') + 1
                WHEN b.rank IN (2,3,4,5) THEN 7
                ELSE LAST_DAY(a.date_key) - MAX(b.dte) OVER (PARTITION BY TO_CHAR(a.date_key,'YYYYMM'))
        END <=3 THEN TO_CHAR(ADD_MONTHS(date_key,1),'yyyymm')
                ELSE TO_CHAR(date_key,'yyyymm')
        END AS yyyymm
        ,CASE   WHEN NVL(b.rank,5) = 1 and CASE   WHEN b.rank = 1 then b.dte-TRUNC(a.date_key,'MONTH') + 1
                WHEN b.rank IN (2,3,4,5) then 7
                ELSE LAST_DAY(a.date_key) - MAX(b.dte) OVER (PARTITION BY TO_CHAR(a.date_key,'YYYYMM'))
        END <=3 THEN 5
                WHEN NVL(b.rank,5) = 5 and CASE   WHEN b.rank = 1 THEN b.dte-TRUNC(a.date_key,'MONTH') + 1
                WHEN b.rank IN (2,3,4,5) then 7
                ELSE LAST_DAY(a.date_key) - MAX(b.dte) OVER (PARTITION BY TO_CHAR(a.date_key,'YYYYMM'))
        END <=3 THEN 1
                ELSE NVL(b.rank,5)
        END AS week_nbr
        FROM dms.date_table a
        LEFT JOIN
                (
                SELECT TO_CHAR(date_key,'YYYYMM') YYYYMM,
                    date_key as dte,
                    day_of_month,
                    RANK() OVER (PARTITION BY month_of_year, year_number ORDER BY day_of_month ) as rank
                FROM dms.date_table
                WHERE day_of_week = 'FRIDAY'
                    AND date_key > TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-6)))
                )b
            ON b.dte-a.date_key  BETWEEN 0 AND 6 AND TO_CHAR(a.date_key,'YYYYMM') = b.YYYYMM
        WHERE a.date_key BETWEEN TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-6))) AND TRUNC(SYSDATE)

     ) d ON b.date_key = d.date_key
WHERE row_date BETWEEN LAST_DAY(ADD_MONTHS(SYSDATE-1,-6)) AND LAST_DAY(SYSDATE-1)
    AND (c.reporting_desc IN ('INBOUND_COLL') OR  a.split = 42129)
GROUP BY row_date
    , CASE WHEN a.split = 42129 THEN 'Direct Check Enhancement' ELSE c.description END
    , CASE WHEN a.split = 42129 THEN 'TAMPA' ELSE c.site END
    , c.inbound_ind
    , TO_NUMBER(TO_CHAR(b.date_key,'mm'))
    , CASE WHEN b.date_key BETWEEN LAST_DAY(ADD_MONTHS(SYSDATE-1,-1)) AND LAST_DAY(SYSDATE-1) THEN 1 ELSE 0 END
    , d.yyyymm
    , d.week_nbr
ORDER BY a.row_date, category
and i have defined a in_range 'Date Range' paramater based on the row.date
and filtering the report on [DT]>=?Date Range? and  [DT]<=?Date Range?

[RubenvdLinden]

Select the data item in the crosstab, then find the 'Text Item' section in the properties list.
There should be a property called 'Source Type'. Make sure it's set to Data Item Value and not to 'Member Value'. Otherwise, the Data Format property will not work.

[mtirpude]

Hi,

Please find attached xml. This will solve your issue. You need to use extract function to extract the day, month and weekday. And then you need to concat these to get the desired result.

Regards,
Manish Tirpude
mtirpude@infocepts.com