Home » SQL & PL/SQL » SQL & PL/SQL » Convert Date in String field (merged)
Convert Date in String field (merged) [message #671762] Tue, 18 September 2018 12:20 Go to next message
ssmith001
Messages: 35
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?
Convert Date in String field [message #671763 is a reply to message #671762] Tue, 18 September 2018 12:21 Go to previous messageGo to next message
ssmith001
Messages: 35
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 #671764 is a reply to message #671762] Tue, 18 September 2018 12:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You compare a date to a string, why don't you just compare the 2 strings? Why converting the column value to a date?

Re: Convert Date in String field (merged) [message #671765 is a reply to message #671764] Tue, 18 September 2018 12:47 Go to previous messageGo to next message
ssmith001
Messages: 35
Registered: August 2018
Member
I actually want to use it as such:

...
Where TO_DATE(SH.RFRC_NUM10,'YYYY-MM-DD') between <date 1> and <date 2>

Can I use BETWEEN if the date is in a string?
Re: Convert Date in String field (merged) [message #671766 is a reply to message #671765] Tue, 18 September 2018 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes you can, you have to convert your constants to a date datatype like you do it for your column or use the Datetime Literals syntax.

Also always post your Oracle version, with 4 decimals, as solution often depends on it.
Re: Convert Date in String field (merged) [message #671767 is a reply to message #671766] Tue, 18 September 2018 13:52 Go to previous messageGo to next message
ssmith001
Messages: 35
Registered: August 2018
Member
Using: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

This is coming back with an error of: ORA-01830

...
AND TO_DATE(SH.RFRC_NUM10,'YYYY-MM-DD') = TO_DATE('2018-08-31','YYYY-MM-DD')
Re: Convert Date in String field (merged) [message #671768 is a reply to message #671767] Tue, 18 September 2018 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select  TO_DATE('2018-08-31','YYYY-MM-DD')  from dual;
TO_DATE('2018-08-31
-------------------
31/08/2018 00:00:00
This proves this part is correct.
So the conclusion is that what you think is in the column is NOT what is actually is (assuming the error actually comes from the line you posted and not another part of the statement).

Re: Convert Date in String field (merged) [message #671771 is a reply to message #671767] Tue, 18 September 2018 18:40 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
ssmith001 wrote on Tue, 18 September 2018 11:52
Using: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

This is coming back with an error of: ORA-01830

...
AND TO_DATE(SH.RFRC_NUM10,'YYYY-MM-DD') = TO_DATE('2018-08-31','YYYY-MM-DD')
I suspect that the column data in SH.RFRC_NUM10 is not what you believe it to be.

post results from the 2 SQL below

SELECT COUNT(*) FROM <YOUR_TABLE> WHERE RFRC_NUM10 IS NULL;

SELECT LENGTH(RFRC_NUM10), COUNT(*) FROM <YOUR_TABLE> GROUP BY LENGTH(RFRC_NUM10);



Re: Convert Date in String field (merged) [message #671799 is a reply to message #671771] Wed, 19 September 2018 12:42 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
As Swan as mentioned... You have a row with a text date that is too long.

e.g.:

DEV1> select to_date('2018-01-091', 'YYYY-MM-DD') from dual;
select to_date('2018-01-091', 'YYYY-MM-DD') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

JP
Re: Convert Date in String field (merged) [message #672002 is a reply to message #671799] Thu, 27 September 2018 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
You should have the common courtesy to stop leave threads like this unresolved & unacknowledged.
Re: Convert Date in String field (merged) [message #672076 is a reply to message #672002] Mon, 01 October 2018 11:56 Go to previous messageGo to next message
Bill B
Messages: 1969
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 #672079 is a reply to message #672076] Mon, 01 October 2018 15:22 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Unless I am missing something here (I probably am), then how can you have a date character string in a VARCHAR(2) datatype as stated by the OP?

[Updated on: Mon, 01 October 2018 15:24]

Report message to a moderator

Re: Convert Date in String field (merged) [message #672080 is a reply to message #672079] Mon, 01 October 2018 17:10 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
As long as the varchar2 string has the date in a specific format by using the to_date function you can easily treat it Like a date field
Re: Convert Date in String field (merged) [message #672086 is a reply to message #672080] Tue, 02 October 2018 04:00 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Joy's pointed out that the stated length - 2 characters - isn't long enough.
I assume that was a typo by the OP.
Re: Convert Date in String field (merged) [message #672087 is a reply to message #671762] Tue, 02 October 2018 07:25 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
Having not heard from the OP since the day he opened this thread (18 Sep) in spite of all the replies and help offered, I assume he has abandoned it.
Re: Convert Date in String field (merged) [message #672151 is a reply to message #672087] Thu, 04 October 2018 07:40 Go to previous messageGo to next message
ssmith001
Messages: 35
Registered: August 2018
Member
My 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.
Re: Convert Date in String field (merged) [message #672154 is a reply to message #672151] Thu, 04 October 2018 08:23 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
ssmith001 wrote on Thu, 04 October 2018 08:40
My 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 #672155 is a reply to message #672154] Thu, 04 October 2018 08:26 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
it is NOT a varchar(2) field, it is a varchar2 field. The first implies a length of 2, the second means a variable length field of type varchar2
Re: Convert Date in String field (merged) [message #672196 is a reply to message #671762] Sat, 06 October 2018 09:43 Go to previous messageGo to next message
dhashmi4
Messages: 2
Registered: October 2018
Junior Member
kindly use first.

1)
TO_DATE(your_column_name,'YYYY-MM-DD') = TO_DATE(to_char('2018-08-31','YYYY-MM-DD'))
and check this is working
Re: Convert Date in String field (merged) [message #672198 is a reply to message #672196] Sat, 06 October 2018 10:01 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
dhashmi4 wrote on Sat, 06 October 2018 07:43
kindly use first.

1)
TO_DATE(your_column_name,'YYYY-MM-DD') = TO_DATE(to_char('2018-08-31','YYYY-MM-DD'))
and check this is working


It is silly & plain wrong to use TO_CHAR on a literal string '2018-08-31' since it already a character string!

You NEVER get a second chance to make a first impression.

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Convert Date in String field (merged) [message #672199 is a reply to message #672151] Sat, 06 October 2018 11:49 Go to previous messageGo to next message
John Watson
Messages: 8351
Registered: January 2010
Location: Global Village
Senior Member
ssmith001 wrote on Thu, 04 October 2018 13:40
My 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 Go to previous message
Bill B
Messages: 1969
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);
Previous Topic: Help with bitmap join indexes
Next Topic: Rownumer WITH OUT Order by
Goto Forum:
  


Current Time: Tue Sep 29 22:43:24 CDT 2020