Unable to Convert to date format

General
Enthusiast

Unable to Convert to date format

Hi Team,

I have requirement, My target should be in date format (below 3 query) ... but getting as invalid date .. please help me

while i was writing target was varchar my query is working file and while writing with where condition the query works fine... where as if you can check my 2nd and 3rd query is same .. just given where conditon in 2nd query(getting o/p).. not getting o/p in 3rd query

1)Query working perfect (Tgt is in Varchar)

sel

CASE

    WHEN A.FIRST_MISC_DATE IS NOT NULL AND B.INV_DUE_DAY IS NOT NULL

    THEN CAST(A.FIRST_MISC_DATE AS VARCHAR(6))|| TRIM(LPAD(CAST(CAST(B.INV_DUE_DAY AS INTEGER) AS VARCHAR(2)),2,'00'))

                WHEN B.INV_DUE_DAY IS NULL AND A.FIRST_MISC_DATE IS NOT NULL

                 THEN cast(LAST_DAY(cast((A.FIRST_MISC_DATE || '01') as date FORMAT 'YYYYMMDD')) as varchar(8))

                END DATE_FRMT

FROM FIN_SVC_STG.INFL_LS_MISC_REP_EMEA A inner join FIN_SVC_STG.INFL_LS_BILLING_EMEA B ON B.CONTRACT_NO=A.MISC_CONTRACT_NO

2) Query getting suceedded (TGT is in Dat format) .. with  where condition

SEL CAST(VIN.DATE_FRMT AS date format 'YYYYMMDD') FROM

(sel

CASE

    WHEN A.FIRST_MISC_DATE IS NOT NULL AND B.INV_DUE_DAY IS NOT NULL

    THEN CAST(A.FIRST_MISC_DATE AS VARCHAR(6))|| TRIM(LPAD(CAST(CAST(B.INV_DUE_DAY AS INTEGER) AS VARCHAR(2)),2,'00'))

                WHEN B.INV_DUE_DAY IS NULL AND A.FIRST_MISC_DATE IS NOT NULL

                 THEN cast(LAST_DAY(cast((A.FIRST_MISC_DATE || '01') as date FORMAT 'YYYYMMDD')) as varchar(8))

                END DATE_FRMT

FROM FIN_SVC_STG.INFL_LS_MISC_REP_EMEA A inner join FIN_SVC_STG.INFL_LS_BILLING_EMEA B ON B.CONTRACT_NO=A.MISC_CONTRACT_NO

     where A.MISC_CONTRACT_NO ='006-0126425-001'

3) Getting Error after removing where condition

SEL CAST(VIN.DATE_FRMT AS date format 'YYYYMMDD') FROM

(sel

CASE

    WHEN A.FIRST_MISC_DATE IS NOT NULL AND B.INV_DUE_DAY IS NOT NULL

    THEN CAST(A.FIRST_MISC_DATE AS VARCHAR(6))|| TRIM(LPAD(CAST(CAST(B.INV_DUE_DAY AS INTEGER) AS VARCHAR(2)),2,'00'))

                WHEN B.INV_DUE_DAY IS NULL AND A.FIRST_MISC_DATE IS NOT NULL

                 THEN cast(LAST_DAY(cast((A.FIRST_MISC_DATE || '01') as date FORMAT 'YYYYMMDD')) as varchar(8))

                END DATE_FRMT

FROM FIN_SVC_STG.INFL_LS_MISC_REP_EMEA A inner join FIN_SVC_STG.INFL_LS_BILLING_EMEA B ON B.CONTRACT_NO=A.MISC_CONTRACT_NO

) VIN

Sample Data

sel FIRST_MISC_DATE  from FIN_SVC_STG.INFL_LS_MISC_REP_EMEA

201208

201201

201308

201210

201208

sel INV_DUE_DAY from FIN_SVC_STG.INFL_LS_BILLING_EMEA

1

1

15

1

1

Column Data Types

FIRST_MISC_DATE NUMBER(10,0)

INV_DUE_DAY DECIMAL(10,0)



1 REPLY
Senior Apprentice

Re: Unable to Convert to date format

There's obviously some bad data.

Check if year/month/day is out of range:

SELECT 
MIN(FIRST_MISC_DATE / 100)
,MAX(FIRST_MISC_DATE / 100)
,MIN(FIRST_MISC_DATE MOD 100)
,MAX(FIRST_MISC_DATE MOD 100)
,MIN(INV_DUE_DAY)
,MAX(INV_DUE_DAY)
FROM FIN_SVC_STG.INFL_LS_MISC_REP_EMEA A inner join FIN_SVC_STG.INFL_LS_BILLING_EMEA B ON B.CONTRACT_NO=A.MISC_CONTRACT_NO

If this is ok you might still have some bad dates like february 30. or june 31.