Convert String to Datetime

596442 Dec 19, 2007 7:50 AM

Currently Being Moderated

I have date and time stored as a STRING datatype in this format.
2007-01-01 12:00:01 AM

Now I want all the results greater than some date and time e.g. "2007-09-01 12:00:01 AM" and for that I need to convert the string datatype to DATETIME datatype to make this mathematical expression work. Any help on how can I achieve this or is there any other workaround?

Thanks

1. Re: Convert String to Datetime

Sven W. Dec 19, 2007 7:52 AM (in response to 596442)

Currently Being Moderated

Apart from the AM/PM problem it looks like you could sort/compare on the string value.

Why was it entered as string in the database? If possible change it there.
Report Abuse

Like (0)
2. Re: Convert String to Datetime

LukaszMastalerz Dec 19, 2007 7:56 AM (in response to 596442)

Currently Being Moderated

There is no DATETIME format in Oracle. In this case you should use TO_DATE(date_col, 'YYYY-MM-DD HH24:MI:SS AM')
Report Abuse

Like (0)
3. Re: Convert String to Datetime

596442 Dec 19, 2007 8:07 AM (in response to Sven W.)

Currently Being Moderated

I can not apply greater than expression on string as it is giving me incorrect results. Also it is not possible to change the datatepe to DATETIME as there are some calculation involved before I store the date and time in string format.

Message was edited by:
user593439
Report Abuse

Like (0)
4. Re: Convert String to Datetime

589667 Dec 19, 2007 8:07 AM (in response to 596442)

Currently Being Moderated

Hi,

How about:
1. performing all your calculations (assuming they are on these "date" strings)
2. obtaining the date + time as strings
3. using TO_DATE to convert these strings to dates, and then
4. storing these dates in your table ?

That way, you will be able to let Oracle handle all date arithmetic on dates.

Cheers,
pratz
Report Abuse

Like (0)
5. Re: Convert String to Datetime

596442 Dec 19, 2007 8:09 AM (in response to LukaszMastalerz)

Currently Being Moderated

I tried to_date but I am getting "ORA - 01843 not a valid month" error
Report Abuse

Like (0)
6. Re: Convert String to Datetime

589667 Dec 19, 2007 8:19 AM (in response to 596442)

Currently Being Moderated

Is the month value a valid one ? What's the string value and the format mask ?

pratz
Report Abuse

Like (0)

7. Re: Convert String to Datetime

589667 Dec 19, 2007 8:21 AM (in response to 596442)

Currently Being Moderated

test@ORA92>

test@ORA92> select to_date('2007-01-01 12:00:01 AM','yyyy-mm-dd hh:mi:ss pm') as dt from dual;



DT

---------

01-JAN-07



test@ORA92>

pratz

8. Re: Convert String to Datetime

596442 Dec 19, 2007 8:48 AM (in response to 589667)

Currently Being Moderated

Hi Pratz,

The string value which is stored in the database is in this format '8/30/2007 1:26:56 PM'
The format Mask is 'mm/dd/yyyy HH:MI:SS AM'

The query which I am using is:
SELECT billed_time FROM bill WHERE billed_time >= to_DATE('01/01/2007 12:00:01 AM','mm/dd/yyyy HH:MI:SS AM')

I am getting not a valid month error when trying to run this query.

Message was edited by:
user593439
Report Abuse

Like (0)
9. Re: Convert String to Datetime

Boneist Dec 19, 2007 8:53 AM (in response to 596442)
Currently Being Moderated

you need to convert the column into date format too!

SELECT billed_time FROM bill WHERE TO_DATE(billed_time, 'mm/dd/yyyy HH:MI:SS AM') >= TO_DATE('01/01/2007 12:00:01 AM','mm/dd/yyyy HH:MI:SS AM');
The issue you're seeing is because the billed_time column is being implicitly converted to a date, based on what your NLS settings are. You should always explicitly convert strings to dates (even the greatest of experts can get caught out by not doing this!)
Report Abuse

Like (0)
10. Re: Convert String to Datetime

596442 Dec 19, 2007 9:06 AM (in response to Boneist)

Currently Being Moderated

Thanks Boneist. This solution seems working great.
Report Abuse

Like (0)

Go to original post