Search code examples
sqloracle-databasedatetype-conversiontimestamp-with-timezone

Converting string to date in sql

I need to convert 2014-11-18T14:08:43+00:00 which is in varchar in my sql developer to date format in order to filter a few entries.

I tried

to_date(LAST_UPDATE_DATE,'YYYY-MM-DD')

but it gives an error

ORA-01830: date format picture ends before converting entire input string.

Kindly help..

Solution

  • Just in case you didn't mean to put up sql server but instead you need to use oracle (seeing as you are using to_date and you are getting an ora exception) I added a quick datetime conversion for date and timestamp (no milliseconds) for your date format:

    SELECT to_Date(concat
               (substr
                (myvar,0,10),
                concat(' ',
                       substr(myvar,12,8)
                      )
               ),'YYYY-MM-DD HH24:mi:ss') AS mydate
FROM mytable

    Fiddle