1

I am stuck with a Oracle query. In my dashboard app, we have to show a summary graph for total of details lines basically. In one particular tab, we need the datetime in the details, but we group on the summary by date only.

How to convert date(string type) into date(date time ) in oracle. I am using select within select in other words nested select. Inner query returns string date time, and outer query unable to convert this to date time

Pinchas K
  • 1,541
  • 3
  • 13
  • 19

2 Answers2

2

You can use TO_DATE function.

For e.g:
If the input string in in the form 12/21/2012
You can try TO_DATE('12/21/2012', 'MM/DD/RRRR')

Check this link for more on variou formats: http://psoug.org/definition/TO_DATE.htm

Chandu
  • 81,493
  • 19
  • 133
  • 134
1

You would use the to_date function to convert a string to a date. For example

to_date( '2012-12-25 13:45:56', 'yyyy-mm-dd hh24:mi:ss' )

or

to_date( '12/25/98', 'MM/DD/RR' )

The format mask that you pass in as the second argument will depend on the format of the string. The set of valid format masks for the to_date function is in the documentation.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • the issue is we are first converting date and time from different tables, then concatenating them then later converting back to date and then only need date. the last step is in outer query and other steps are in inner query – Pinchas K Jul 13 '12 at 13:15
  • @PinchasK - It's not obvious to me what the problem is. Are you saying that the outer query needs to convert various differently formatted strings to a date? So the first row might be in `YYYY-MM-DD` format, the next row in `mm/dd/rr hh24:mi:ss` format, etc? – Justin Cave Jul 13 '12 at 14:26