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 Dec 28 '15 at 10:10 — Rajesh, Dec 28 '15 at 10:10

score 2 · Accepted Answer · answered Dec 28 '15 at 10:13

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 '15 at 10:13

user2232273

4,898
15
49
75

1

This works, as long as you add the conversion type: SELECT CONVERT(REPLACE(REPLACE('2015-12-23T09:57:00.000Z','T', ' '), 'Z', ''), DATETIME); – SPlatten Dec 28 '15 at 10:42

score 1 · Answer 2 · answered Dec 28 '15 at 10:14

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 '15 at 10:14

Rudolf Gröhling

4,611
4
27
37

score 0 · Answer 3 · answered Dec 30 '15 at 00:43

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

Javascript date/time string to sql date time

3 Answers3