0

i have a timestamp column value like "07-JUN-17 11.00.00.000000000 AM" and have to convert to date value like "07-JUN-17 11.00.00 AM".

jayanth
  • 25
  • 1
  • 5
  • value should be a valid date – jayanth Apr 16 '18 at 17:12
  • i tried cast(ARRIVAL_TS as date) here in sql developer it gives only date but not contains time – jayanth Apr 16 '18 at 17:17
  • It does contain time component, you could use `TO_CHAR()` to see the output in whichever format u want. But, remember, don't convert the table column itself to `VARCHAR2`. – Kaushik Nayak Apr 16 '18 at 17:20
  • if we use to_char() function here, do we have to convert it back to TO_DATE..?? – jayanth Apr 16 '18 at 17:23
  • 2
    No. Please understand.. In Oracle, date type **contains** time component, but it is not necessarily displayed when you run the select query on the column, unless your `NLS_DATE_FORMAT` session parameter is set to show the time. So, either you could modify the session parameter or convert it to a character using `TO_CHAR` to **see** it in desired format. – Kaushik Nayak Apr 16 '18 at 17:30
  • Thanks for quick response – jayanth Apr 16 '18 at 17:35
  • 1
    @KaushikNayak - It is always best to modify NLS_DATE_FORMAT, not the output of the query (unless "the query" is simply for final reporting purposes). In most cases the developer wants to see the value - to convince himself the values are correct - but the value produced by the processing is often used in further processing. So the output should remain in its correct data type (in this case, DATE vs. VARCHAR2). –  Apr 16 '18 at 18:48

0 Answers0