Convert Date in String field (merged) [message #671762] |
Tue, 18 September 2018 12:20 |
|
ssmith001
Messages: 37 Registered: August 2018
|
Member |
|
|
I have a VARCHAR(2) column (SH.RFRC_NUM10) that stores a date in this format (YYYY-MM-DD). I don't have any ability to change the data type, just need to work with what I'm given. I need to use the date in the field in my Where clause as such
Where TO_DATE(SH.RFRC_NUM10,'YYYY-MM-DD') = '2018-08-31'
and this isn't working (throws this error: ORA-01830: date format picture ends before converting entire input string). What am I doing wrong?
|
|
|
|
|
|
|
|
|
|
|
|
Re: Convert Date in String field (merged) [message #672076 is a reply to message #672002] |
Mon, 01 October 2018 11:56 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Run the following code to find out where the problem is
select *
from your_table
where NOT regexp_like(RFRC_NUM10,'^\d{4}-\d{2}-{2}\d{2}$');
This query will return any record where the column is not 4 number followed by a dash followed by 2 numbers followed by a dash then 2 numbers
|
|
|
|
|
|
|
|
Re: Convert Date in String field (merged) [message #672154 is a reply to message #672151] |
Thu, 04 October 2018 08:23 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
ssmith001 wrote on Thu, 04 October 2018 08:40My apologies to all. I was able to determine, with help from all above, that the error was due to some goofy dates that were in the varchar(2) field.
One time, it's a typo, more than once it just plain ignorance.
|
|
|
|
|
|
Re: Convert Date in String field (merged) [message #672199 is a reply to message #672151] |
Sat, 06 October 2018 11:49 |
John Watson
Messages: 8929 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
ssmith001 wrote on Thu, 04 October 2018 13:40My apologies to all. I was able to determine, with help from all above, that the error was due to some goofy dates that were in the varchar(2) field. Your goofy dates can be handled. For example,pdby1>
pdby1> select validate_conversion('29-02-2017' as date,'dd-mm-yyyy') from dual;
VALIDATE_CONVERSION('29-02-2017'ASDATE,'DD-MM-YYYY')
----------------------------------------------------
0
pdby1> select validate_conversion('28-02-2017' as date,'dd-mm-yyyy') from dual;
VALIDATE_CONVERSION('28-02-2017'ASDATE,'DD-MM-YYYY')
----------------------------------------------------
1
pdby1> select to_date('garbage' default '01-01-2018' on conversion error, 'dd-mm-yyyy') from dual;
TO_DATE('GARBAGE'DE
-------------------
2018-01-01:00:00:00
pdby1>
|
|
|
Re: Convert Date in String field (merged) [message #672446 is a reply to message #672199] |
Mon, 15 October 2018 14:09 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Once you get your data repaired you can put in a check constraint that will stop invalid formatted dates from being put in
CREATE TABLE TEST
(
MYDATE VARCHAR2(20 BYTE)
)
TABLESPACE USERS_BIG
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);
ALTER TABLE TEST ADD (
CONSTRAINT TEST_C01
CHECK (regexp_like(mydate,'^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$'))
ENABLE VALIDATE);
|
|
|