How to Convert string to date

I have a date saved in varchar2 colomn.

how can I convert the vaules into date, so I can use the date to compare data.

Ex: I have the value '20-03-2007 05:31:29', but this value is saved as varchar2.

how can I take from this value the date '20-03-2007' as date format?

By using the aptly named to_date() function. This is very bad, please review your database design and use proper datatypes.

I already used to_date function, but it caused some errors.

also I used to_char function with to_date.

I know this is bad, but this data is old, and i'm trying manipulte it.

many errors like: invalid number, not a valid month,..

Then show us

- Your SQL statement
- Your input data
- The exact error message

this is the query and the error:

1 Select to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy'),
2 count(BATCHNAME) Batch_Count, Sum(DOCCOUNT) Doc_Count,
3 sum(PAGECOUNT) Page_Count
4 From reports
5 Where OPERATION = 73
6 And to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
7 between '09-04-2007' And '15-04-2007'
8 Group by to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy'),OPERATION
9* Order By to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
SQL> /
between '09-04-2007' And '15-04-2007'
*
ERROR at line 7:
ORA-01843: not a valid month


note: OP_DATETIME is the varchar2 field, contains data like '15-03-2007 06:09:58'


another error when using the following query:

1 Select to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy'),
2 count(BATCHNAME) Batch_Count, Sum(DOCCOUNT) Doc_Count,
3 sum(PAGECOUNT) Page_Count
4 From reports
5 Where OPERATION = 73
6 And to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
7 between to_date('09-04-2007','dd-mm-yyyy') And to_date('15-04-2007','dd-mm-yyyy')
8 Group by to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy'),OPERATION
9* Order By to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
SQL> /
And to_date(to_date(OP_DATETIME,'dd-mm-yyyy HH12:MI:SS'),'dd-mm-yyyy')
*
ERROR at line 6:
ORA-01861: literal does not match format string

You NLS_DATE_FORMAT is not set to MM-DD-YYYY.
You should always use to_date with a format model as you did in the second query.
obviously it does not, otherwise you wouldn't get the error message literal does not match format string
What is your intention withi this? Why do you convert a date column to a date column? If you want to "strip" off the time portion use the trunc() function.

But most important: why on earth are you storing a date in a VARCHAR" column? You are now paying the price for this mistake.

I haven't tested this (I don't have PHP installed on my server), but I think this should work. If not, I think I've commented it well enough for you to figure out what I'm doing.

Note that this converts to the UNIX time format (an integer), not an instance of the date class. If you've got enough PHP knowledge to understand this code, though, you should be able to fix that problem very easily.

Enjoy, and I hope it works for you!



EDIT -
Oh.. am I really that ignorant that this isn't a conversation about PHP? Hehe, I hope I'm wrong about that, but if I'm right, sorry!