Link to home
Start Free TrialLog in
Avatar of Mike McCracken
Mike McCracken

asked on

Converting a date to a string in Oracle

I am trying to retrieve data from a database.  The dates are stored as strings in the format of yyyy-mm-dd HH24:mm:ss.sss.

I want to allow the users to enter a date at a prompt then convert the entered value to that format.

I have tried To_Char(Userdate, 'yyyy-MM-dd HH24:MI:SS')

I get an error when I run a query.  Database Error: ORA-01722:Invalid Number

mlmcc
Avatar of dsacker
dsacker
Flag of United States of America image

Please post the date value(s) that causes the error.
ASKER CERTIFIED SOLUTION
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Try this:
select to_timestamp('2015-01-01 12:12:12.111','yyyy-mm-dd HH24:mi:ss.ff') from dual
/

Dates do not have fractional seconds.  Timestamps do.
Avatar of Mike McCracken

ASKER

WHen I look at the query that is generated it is

SELECT
  Audit_Date_Start.DateStamp,
'01-06-2015 00:00:00',
'17-06-2015 00:00:00',
To_Char(('01-06-2015 00:00:00' ), 'YYYY-MM-DD HH24:MI:SS')
FROM
 D_Date  Audit_Start_Date

mlmcc
to_char is converting a date to a string, your userdate is a string already a string from what I understand.

Do you want your users to enter a date (string) and then search for that in the database?
In the report the second and third lines are dates and can be formatted as such.

I want to allow the users to select from the calendar rather than having to enter a string in the right format.

mlmcc
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The "date string" in the To_Char is the answer to a prompt, it is selected from the calendar and is the same prompt that the user entered the 1 Jun date on the second line.

The query shown is after the reporting tool has translated the user inputs but before it is sent to the database.

mlmcc
>>I want to allow the users to select from the calendar rather than having to enter a string in the right format.

Select from an object or manually enter it:  Once you decide on the format passed to Oracle, just provide the correct format mask.

That said:
If the data in the database is stored as a string, do you need to convert the provided parameters into a date at all?
>>The query shown is after the reporting tool has translated the user inputs but before it is sent to the database.

Then the 'translator' is broken.  The string and format mask MUST match up or Oracle cannot convert the string to a date.
The query I wrote has 2 prompts in it.  One for a start date and the other for the end date.

SELECT
    Audit_Start_Time,  PromptForStartDate, PromptForEndDate
FROM AUDITTable

This works and the dates returned from the prompts are dates in the report and can be formatted as such.

WHen I add the attempted conversion I get an error
SELECT
    Audit_Start_Time,  PromptForStartDate, PromptForEndDate,
   ToChar(PromptForStartDate, 'yyyy-MM-dd HH24:MI:SS')
FROM AUDITTable

This is a query generated by the reporting tool WebI based on a universe I am building.

I can quite easily make the prompt for the dates be strings and force the user to enter 2015-06-01 00:00:00 rather than using the calendar to get the date.  I have found users have trouble entering dates in the correct format which is essential in this case since entering 2015-6-1 for the date will return the wrong data and most likely no data.

mlmcc
I agree that selecting from a calendar object is the only way to go.

I'm getting lost in the rest of it..........

First thing you mention is:
The dates are stored as strings in the format of yyyy-mm-dd HH24:mm:ss.sss.

Now you are talking about prompts that are dates.

I really am of no help when it comes to Business Objects/Crystal Reports.

>>the dates returned from the prompts are dates

If it is an actual date data type then to_char should work every time.

You get invalid months when it is either:
A problem with explicitly converting a string to a date.
or
There is actually invalid data in the date field.
The user wants to be able to filter the data for the report by a date range.

Currently all dates in the database are stored as strings in a useful format YYYY-MM-DD 00:00:00.000
Times are stored separately.

I want to prompt the user for a start date and end date (as dates), convert the results of the prompts to strings for comparison with the database.  Obviously 2 conversions (date to string) done before the query starts is better than converting the date field for each row from a string to a date (and depending on optimization perhaps 2 conversions per row).

On the report the result of the prompts are actual dates.  

It may be that in the To_Char function it is a string.  In that case I probably need to use substring to pull it apart and put it back together.

mlmcc
I just ran another test on the prompt.  It appears that internal to the query the result of the date prompt is a string since I changed the To_Char to Length and got 19 as the result.

It may be that I have to convert it to a date then back to a string.

mlmcc
>>database are stored as strings in a useful format YYYY-MM-DD 00:00:00.000

Useful?  Not really.  You are VERY limited with what you can do with strings versus dates.

Remember in my first post:  You really cannot deal with 'date' in Oracle because your values have fractional seconds.  As long as you account for that, you might get away with dates in your queries.

You might need to deal with the timestamp data type instead of date.

>>Times are stored separately.

Silly design but to each their own...

>>In that case I probably need to use substring to pull it apart and put it back together.

Shouldn't be a need.  I would go with your second option (to date then back to string) or my third option.

>>It may be that I have to convert it to a date then back to a string.

Possibly.  You might look to see if you can set the calendar objects 'format' to get it correct to start with?

What I would look at doing is converting your prompted 'strings' to timestamps and in the query convert the stored 'dates' and 'times' into an oracle timestamp with to_timestamp.

In a nutshell:
Treat strings as strings, numbers as numbers and dates as dates.

Don't do queries that have 'dates' formatted as strings and try to compare strings.

If there is an index on the database varcahrf2 field, you'll need to create a new one for the to_timestamp call.  These are called Function-Based Indexes.
Thanks for sticking with me.  I got it figured out.  

To_Char(To_Date(PromptForDate, 'DD-MM-YYYY HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')

I don't know what indexes are on the tables.

mlmcc
Sticking is what I do...

Make sure you write the rest of the queries properly or you'll get bad data.  The data has sub-seconds, your newly formed string doesn't.
I know that but we are looking for ranges not equal on dates so the subseconds don't matter.  Also in that table all times are 00:00:00.000

I tried using the ff3 for the format and it didn't like it.  Complained about the date string format.

Since they are all 000 for the subseconds if it becomes and issue I will just add it to the string.

As far as I know the column is not an index.  The indexed column is a number representing the date a yyyymmdd.  I created another formula that generates the number so I figure to use that for the queries.

mlmcc
>>I tried using the ff3 for the format and it didn't like it.

TO_DATE would complaint.  Did you use TO_TIMESTAMP?

>>The indexed column is a number representing the date a yyyymmdd.

MAN this is a bad design...  Likely created by a non-database person or at a minimum a non-Oracle person...

So you have at least 3 different columns all based on different pieces and parts of the same 'date' values?  And this is considered 'useful'?
I didn't design it just have to work with it.

The ff3 was in the To_Char function trying to add the .000 to the end.  I realized I needed that since the end date of the range was excluded since with the .000 on the end it is > the date without the .000.
Fixed by using the numbers.  

Also the number is the PK so it will be faster for retrieving data.

Thanks for all the help.

mlmcc