-
Maybe someone may know the answer to this:
I've been getting some invalid/ strange date formats in one of my tables
nls_date_format=DD-MON-RRRR
SELECT BH_FAST_ACCESS,
BH_UPD_USER,
BH_UPD_DATE, TO_CHAR(BH_REL_DATE)
FROM BOM_HDR WHERE TO_CHAR(BH_REL_DATE)='00-DECEMBER-00';
BH_FAST_AC BH_UPD_U BH_UPD_DA
---------- -------- ---------
TO_CHAR(BH_REL_DATE)
--------------------------------
MT00407093 HBA5919 13-DEC-00
00-DECEMBER-00
MT00406895 HBA5919 13-DEC-00
00-DECEMBER-00
MT00407114 HBA5919 13-DEC-00
00-DECEMBER-00
SELECT BH_FAST_ACCESS,
BH_UPD_USER,
BH_UPD_DATE, BH_REL_DATE
FROM BOM_HDR WHERE TO_CHAR(BH_REL_DATE)='00-DECEMBER-00';
BH_FAST_AC BH_UPD_U BH_UPD_DA BH_REL_DA
---------- -------- --------- ---------
MT00406981 HBA5919 13-DEC-00 00-DECEMB
MT00407000 HBA5919 13-DEC-00 00-DECEMB
MT00407027 HBA5919 13-DEC-00 00-DECEMB
MT00407041 HBA5919 13-DEC-00 00-DECEMB
MT00407066 HBA5919 13-DEC-00 00-DECEMB
This is on a 7.3.4 database NT4.0 platform.
-
Check the data type for column BH_REL_DATE. I think it's not DATE and may be VARCHAR2. The values you are getting might be inserted by the application as VARCHAR not DATE.
Let me know your findings.
Sanjay
