Converting string to date in sql

Question

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..

what version of sql server are you running? for me it says to_date isn't recognized — Jeremy C., Jun 09 '15 at 06:50

score 0 · Answer 1 · answered Jun 09 '15 at 06:51

0

declare @varDate as nvarchar(50) = '2014-11-18T14:08:43+00:00'

select CAST(substring(@varDate,0,CHARINDEX('T',@varDate)) as date)

answered Jun 09 '15 at 06:51

ThePravinDeshmukh

1,823
12
21

3

How could sql server syntax work for an Oracle database? – Lalit Kumar B Jun 09 '15 at 07:19
@LalitKumarB I'm wondering the same thing, or how she got ora error when the tag is sql server, this is really confusing – Jeremy C. Jun 09 '15 at 07:22
it was tagged previously with `sql` `sql-server`, it later has edited – ThePravinDeshmukh Jun 09 '15 at 07:27

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

Either you can use it like this

declare @Date as nvarchar(100) = '2014-11-18T14:08:43+00:00'
SELECT CONVERT(DATE,@Date) AS Date

OR go for this answer which is accepted in this question

ORA-01830: date format picture ends before converting entire input string / Select sum where date query

edited May 23 '17 at 12:22

Community

1
1

answered Jun 09 '15 at 06:57

Chiragkumar Thakar

3,616
5
37
49

@Isha try this solution – Chiragkumar Thakar Jun 09 '15 at 07:03

score 0 · Answer 3 · edited Jun 09 '15 at 07:24

0

You could try this;

Select CAST ('2014-11-18T14:08:43+00:00' as date)

The assumption is you are in SQL Server 2012

edited Jun 09 '15 at 07:24

shA.t

16,580
5
54
111

answered Jun 09 '15 at 07:04

Mr Robbes

49
4

score 0 · Answer 4 · answered Jun 09 '15 at 07:14

ORA-01830: date format picture ends before converting entire input string. to_date(LAST_UPDATE_DATE,'YYYY-MM-DD')

2014-11-18T14:08:43+00:00 is TIMESTAMP and not DATE.

First of all, you should never ever store DATE/TIMSTAMP as string. It is a database design flaw.

Anyway, you could convert it to TIMESTAMP WITH TIMEZONE.

For example,

SQL> SELECT to_timestamp_tz('2014-11-18T14:08:43+00:00',
  2                         'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM')
  3                         AS tm_stamp
  4  FROM dual;

TM_STAMP
----------------------------------------------------------------
18-NOV-14 02.08.43.000000000 PM +00:00

SQL>

@Isha `ORA` error proves that your database is `Oracle` and not `sql server`. — Lalit Kumar B, Jun 09 '15 at 07:21

score 0 · Accepted Answer · answered Jun 09 '15 at 07:21

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

Converting string to date in sql

5 Answers5