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.
newtoperlpgmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>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.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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:

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

http://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
0
slightwv (䄆 Netminder) Commented:
>>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:
http://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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

newtoperlpgmAuthor Commented:
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.
0
PortletPaulfreelancerCommented:
TRUNC(to_date(varchar2date,'MM/DD/YYYY HH24:MI') )

TRUNC() is the function to use to 'remove'* time from a date, or round to larger units, e.g.
SELECT
      sysdate
    , TRUNC(sysdate)                                   AS without_time
    , TO_CHAR(TRUNC(sysdate), 'YYYY-MM-DD HH24:MI:SS') AS without_time_masked
    , TRUNC(sysdate,'YYYY')                            AS trunc_to_year
    , TRUNC(sysdate,'MM')                              AS trunc_to_month
FROM dual

Open in new window

see: Oracle/PLSQL: TRUNC Function (with dates)
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm

* more like setting the time to zeros
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
trunc is probably the easiest/best.

Just wanted to throw out alternatives:
Just grab the date portion from the string.  There are several ways to do this depending on the exact data you have.

If the date is ALWAYS 10 characters:
to_date(substr('08/03/2012 12:38',1,10), 'mm/dd/yyyy')
0
newtoperlpgmAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.