Javascript date/time string to sql date time

Question

I am passing a date time:

    2015-12-23T09:57:00.000Z

To a stored procedure in mariaDB. The stored procedure doesn't like the javascript date/time, how do I convert the date/time for use in the stored procedure?

I've been playing around with 'str_to_date':

    select str_to_date('2015-12-23T09:57:00.000Z', '%Y-%M-%dT%h:%i:%s');

However this returns (NULL).

Try sending it as string using Date.toLocaleString()

Rajesh Dixit
– Rajesh Dixit

2015-12-28 10:10:14 +00:00
Commented Dec 28, 2015 at 10:10 — Rajesh Dixit
– Rajesh Dixit, Commented Dec 28, 2015 at 10:10

user2232273 · Accepted Answer · 2015-12-28 10:13:18Z

2

1) Alternative pass string into stored procedure and then in sql convert to datetime like this:

select CONVERT(DATETIME,REPLACE(REPLACE('2015-12-23T09:57:00.000Z','T',' '),'Z',''))

answered Dec 28, 2015 at 10:13

user2232273

5,00416 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SPlatten Over a year ago

This works, as long as you add the conversion type: SELECT CONVERT(REPLACE(REPLACE('2015-12-23T09:57:00.000Z','T', ' '), 'Z', ''), DATETIME);

Rudolf Gröhling · Accepted Answer · 2015-12-28 10:14:51Z

1

You can use timestamp, in JS get UNIX timestamp like so

var now = new Date(),
    unixTimestamp = Math.floor(now.getTime() / 1000);
    // JS operates with miliseconds, divide by 1000 to get seconds

And in mariaDB use function FROM_UNIXTIME(timestamp)

answered Dec 28, 2015 at 10:14

Rudolf Gröhling

4,8255 gold badges30 silver badges37 bronze badges

Comments

Rick James · Accepted Answer · 2015-12-30 00:43:34Z

Your %M should have been %m.

Another approach:

+-----------------------------------------------------------------------+
| str_to_date(LEFT('2015-12-23T09:57:00.000Z',19), '%Y-%m-%dT%h:%i:%s') |
+-----------------------------------------------------------------------+
| 2015-12-23 09:57:00                                                   |
+-----------------------------------------------------------------------+

If you have 5.6.4 or later:

+-----------------------------------------------------------------+
| str_to_date('2015-12-23T09:57:00.987Z', '%Y-%m-%dT%h:%i:%s.%f') |
+-----------------------------------------------------------------+
| 2015-12-23 09:57:00.987000                                      |
+-----------------------------------------------------------------+

Collectives™ on Stack Overflow

Javascript date/time string to sql date time

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related