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
LVL 101
mlmccAsked:
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.

dsackerContract ERP Admin/ConsultantCommented:
Please post the date value(s) that causes the error.
0
Steve WalesSenior Database AdministratorCommented:
If the date is stored as a string and you're allowing the user to enter a string, you could just do a direct match of string to string.

Otherwise you want to convert to a date first then back to string perhaps:

to_char(to_date(user_input_string, '<format mask>'), '<format mask back to string>')
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:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mlmccAuthor Commented:
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
0
Gerwin Jansen, EE MVETopic Advisor Commented:
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?
0
mlmccAuthor Commented:
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
0
slightwv (䄆 Netminder) Commented:
>>To_Char(('01-06-2015 00:00:00' ), 'YYYY-MM-DD HH24:MI:SS')

Look at the format of the numbers and the format mask you gave.  They don't match up.

Try:
To_Char(('01-06-2015 00:00:00' ), 'MM-DD-YYYY HH24:MI:SS')
0
mlmccAuthor Commented:
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
0
slightwv (䄆 Netminder) Commented:
>>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?
0
slightwv (䄆 Netminder) Commented:
>>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.
0
mlmccAuthor Commented:
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
0
slightwv (䄆 Netminder) Commented:
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.
0
mlmccAuthor Commented:
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
0
mlmccAuthor Commented:
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
0
slightwv (䄆 Netminder) Commented:
>>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.
0
mlmccAuthor Commented:
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
0
slightwv (䄆 Netminder) Commented:
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.
0
mlmccAuthor Commented:
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
0
slightwv (䄆 Netminder) Commented:
>>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'?
0
mlmccAuthor Commented:
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
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.