asked on

Convert varchar2 date into a date format

I have a date that is a varchar2 and I would like to make it a date field. I created a second field named datefield and want to insert into datefield as select from varchar2field. Do I need a specific format or is to_date all I need. I am trying to avoid any Oracle errors when I try to create a view to select the datefield, such as not a valid month, or ORA-01858: a non-numeric character was found where a numeric was expected.

varchar2date
08/03/2012 12:38
09/25/2013 09:46
08/23/2012 14:36

Additionally, does anyone know of a great reference to learning about Oracle date and learning to love Oracle date, I am interested in learning, living, and never again having problem with Oracle date, no more Ora errors. Thank you for your help.

slightwv (䄆 Netminder)

>>Do I need a specific format or is to_date all I need

Always best to add the format:
to_date(varchar2date,'MM/DD/YYYY HH24:MI')

>>I am trying to avoid any Oracle errors when I try to create a view to

You might have errors converting the varchar2 to a date. That can be handled in a pl/sql script to do the conversion.

I'm not sure where the view comes in. Once the varchar2 is converted to the date field in the table, selecting it will never throw an error unless there is a bad format mask when you select it back out to a varchar2.

Alex [***Alex140181***]

You'll need a format mask. Try this one:

select to_date('08/03/2012 12:38', 'mm/dd/yyyy hh24:mi') from dual;

Open in new window

For reference, look here:

https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_11210-Beware-of-Between.html

http://www.oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals.php

slightwv (䄆 Netminder)

>>does anyone know of a great reference to learning about Oracle date

I would start with the online docs.

An Expert here wrote a pretty good Article on it:
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don't-be-scared-and-do-it-right-the-first-time.html

As for loving it, that comes with, pardon the pun, time.

Once you understand how they work, they aren't bad.

newtoperlpgm

ASKER

I'd really like to just obtain the date, and disregard the time. I tried that with to_date(varchar2date,'MM/DD/YYYY HH24:MI') minus the HH24:MI and it of course gave me the ORA-01830: date format picture ends before converting entire input string

How can I achieve this?
Thanks.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

newtoperlpgm

ASKER

I was able to use both to_date(substr('08/03/2012 12:38',1,10), 'mm/dd/yyyy')
and TRUNC(sysdate) AS without_time with my data to achieve my desired date results. Thank you both.