Toad World® Forums

Trying to add year and month filed from date field.

I have a script and i can get it to run with no problem. I need to add new columns for month year and week to my report. I have tried to add this but with no luck. I am sure its a minor thing but need some help figuring this out.

– Weekly Extract of RGA completed claims from GOMS for calculating DPMO

– 111229 J Green

– Modify date range below before running to get the correct week

SELECT RETURN_HEADERS.RETURN_NO,

RETURN_DETAILS.CUSRET_REASON_CODE RETCODE,

RETURN_DETAILS.QTY_RETURNED QTYRET,

RETURN_DETAILS.PART_NO,

PART_DESCRIPTION.DESCRIPTION,

PART_DESCRIPTION.SHORT_DESCRIPTION SHORTDESC,

OECUSTOMER_BILLING.BILL_TO_NAME,

RETURN_HEADERS.CUSTOMER_NO,

RETURN_DETAILS.UNIT_PRICE,

TRUNC(**RETURN_HEADERS.RECORD_DATE) RECDATE**,

– TWO COLUMN PLACE HOLDERS TO MATCH SHIP DISC EXTRACT

RETURN_HEADERS.MANIFEST_NO MANIFEST,

RETURN_HEADERS.MANIFEST_NO SHIPTYPE,

PART_DESCRIPTION.FIN_RESP_CODE FRC,

PART_DESCRIPTION.FIN_BUSINESS_CODE FBC,

CUSTOMER_TYPES_F.CUSTOMER_TYPE_CODE

FROM (RETURN_DETAILS INNER JOIN RETURN_HEADERS ON RETURN_DETAILS.RETURN_NO = RETURN_HEADERS.RETURN_NO)

INNER JOIN OECUSTOMER_BILLING ON RETURN_HEADERS.CUSTOMER_NO = OECUSTOMER_BILLING.CUSTOMER_NO,     PART_DESCRIPTION,

CUSTOMER_TYPES_F

– Enter Monday Date DD-MMM-YYYY thru Sunday Date

WHERE RETURN_HEADERS.CUSTOMER_NO <> ‘05000’

AND RETURN_HEADERS.CUSTOMER_NO <> '04429'

AND RETURN_DETAILS.CUSRET_REASON_CODE NOT IN ('ANNUAL','XREF','TEST','TECH','ERROR','RECALL','MFG','MKT','MKTPRO','QA')

AND RETURN_HEADERS.RECORD_DATE BETWEEN '7-sep-2015' and '14-sep-2015' 

AND RETURN_HEADERS.RETURN_STATUS_TYPE = 3

AND RETURN_HEADERS.RETURN_TYPE IN (0,2)

AND PART_DESCRIPTION.PART_NO = RETURN_DETAILS.PART_NO

AND OECUSTOMER_BILLING.CUSTOMER_TYPE = CUSTOMER_TYPES_F.CUSTOMER_TYPE

What database do you connect to?

To_Char(RETURN_HEADERS.RECORD_DATE, ‘Month’) for the month and To_Char(RETURN_HEADERS.RECORD_DATE, ‘WW’) for week of the year or To_Char(RETURN_HEADERS.RECORD_DATE, ‘W’) for week of the month.

I assume you are using an Oracle database. If not post what you are connecting to.