Javascript date to sql date object

Question

I'm trying to write a query that takes a Javascript date object and then puts it in an object type that is recognized by both SQL Server and Oracle database types.

The issue is that I'm using webservices. So it has to be a string, not an actual passed parameter. Here's what I mean:

var date = new Date();
var firstDayOfMonth = new Date(date.getFullYear(), date.getMonth(), 1);
var lastDayOfMonth = new Date(date.getFullYear(), date.getMonth() + 1, 0);

var webServicesQueryWhereClause = 'readDate BETWEEN '+firstDayOfMonth+' AND '+lastDayOfMonth;

Except firstDayOfMonth and lastDayOfMonth are surrounded by something like to_date() to actually put them in a date format that the databases can read. For example:

var webServicesQueryWhereClause = 'readDate BETWEEN to_date('+firstDayOfMonth+') AND to_date('+lastDayOfMonth+') ';

What should I use to put those dates in a form that can be read by both SQL Server and Oracle?

How is this being executed? Which library are you using? It's generally best to leave it up to the DB to decide how to translate a date object to a recognised format (however, that may not be possible in your case). — James, Nov 19 '13 at 22:52
I'd suggest building the query string in your web service layer, unless this is node.js code. If you use asp.net, take a look at: http://momentjs.com/docs/#/parsing/asp-net-json-date/ — guiomie, Nov 19 '13 at 22:54
@James I'm using the atmosphere library and javax.jws server side to handle the web services. — Graham, Nov 19 '13 at 22:57
@guiomie That's not an option. This query string being run from an app created with Sencha. — Graham, Nov 19 '13 at 22:59
All SQL databases can read the ISO date format unambiguously: ("YYYY-MM-DD HH:mm:ss") `firstDayOfMonth.toISOString()` should do the trick. — Curt, Nov 19 '13 at 22:51
@Graham For the future reference, `moment().format('YYYY-MM-DD HH:MM:SS')` — jithil, Dec 04 '20 at 08:56

score 70 · Accepted Answer · edited Jan 05 '21 at 03:05

70

Have you tried the solutions presented here:

Convert JS date time to MySQL datetime

The title should be called

"Convert JS date to SQL DateTime"

I happened to need to do the same thing as you just now and I ran across this after your question.

This is from the other post by Gajus Kuizinas for those who want the answers on this page:

var pad = function(num) { return ('00'+num).slice(-2) };
var date;
date = new Date();
date = date.getUTCFullYear()         + '-' +
        pad(date.getUTCMonth() + 1)  + '-' +
        pad(date.getUTCDate())       + ' ' +
        pad(date.getUTCHours())      + ':' +
        pad(date.getUTCMinutes())    + ':' +
        pad(date.getUTCSeconds());

or

new Date().toISOString().slice(0, 19).replace('T', ' ');

The first one worked for me. I had a reference problem with the toISOString as well although I would prefer the one liner. Can anyone clarify how to use it and know the limitations on where one can reference it? Good luck!

edited Jan 05 '21 at 03:05

Mark Skelton

3,663
4
27
47

answered Jan 31 '14 at 14:30

JPK

1,324
2
14
25

1

`toISOString` support is IE9+ http://kangax.github.io/compat-table/es5/#Date.prototype.toISOString – Justin Jun 29 '15 at 17:38
4

question was on MS SQL ( T-sql) not mysql :/ – Tom Stickel May 24 '17 at 21:55
1

The first one worked for me, but it has a typo: for each "pad" there must be a closing of the function call, ie one ")". – Jonas Rotilli Aug 12 '19 at 12:03
Careful with `toISOString`: a same date can return different results according to your timezone and day time! Example: now is june 29 at 22:58 (UTC+2) and `new Date(2023, 5, 29).toISOString()` is returning `2023-06-28T22:00:00.000Z` – maxime schoeni Jun 29 '23 at 21:01

score 7 · Answer 2 · edited Aug 18 '21 at 05:32

7

using MomentJs it will be pretty easy.

moment().format('YYYY-MM-DD HH:mm:ss')

https://momentjs.com/

edited Aug 18 '21 at 05:32

Corylus

736
5
16

answered Dec 04 '20 at 08:54

jithil

1,098
11
11

score 5 · Answer 3 · edited Dec 21 '22 at 21:07

5

In my case I was trying to get a TIMESTAMP and store it to a variable so I can pass that array inside a SQL query(Row insertion)

The following worked for me quite well.

var created_at = new Date().toISOString().slice(0, 19).replace('T', ' ');

edited Dec 21 '22 at 21:07

Arjun G

2,194
1
18
19

answered Sep 13 '21 at 16:58

Hashir Qadeer

71
1
5

2

There's already a answer with this exact snipet – jean Sep 13 '21 at 18:55

Simon Borsky · Answer 4 · 2022-03-10T13:06:01.543

2

Maybe I found a bit shorter approach - tested on MSSQL and Postgres

const date = (new Date()).toLocaleString("en-US")

Enjoy!

edited Mar 10 '22 at 13:06

answered Sep 01 '20 at 11:08

Simon Borsky

4,979
2
22
20

4

It doesn't work for me. The return value is "6/30/2021, 2:39:16 PM" while It needs to be of type "2021-06-30 2:39:16 PM". – Mehrdad Nazmdar Jun 30 '21 at 10:11

score 1 · Answer 5 · answered Sep 02 '22 at 19:57

1

I use:

const datetimeSQL = new Date().toISOString().split('T').join(' ').split('Z').join('');
// return '2022-09-02 19:54:17.028'

answered Sep 02 '22 at 19:57

pedro.caicedo.dev

2,269
2
16
19

1

I think new Date().toISOString().replace(/T/, ' ').replace(/Z/, '') would be more efficient. – timkay Oct 25 '22 at 04:35
Thanks @timkay, i'll try it the next time :+1 – pedro.caicedo.dev Oct 26 '22 at 11:35

score 1 · Answer 6 · answered Mar 01 '23 at 13:01

1

Slice off the .sssZ (milliseconds and Z UTC timezone) and replace the T with a space:

console.log(
  new Date().toISOString().slice(0, -5).replace('T', ' ')
)

answered Mar 01 '23 at 13:01

Dominic

62,658
20
139
163

Javascript date to sql date object

6 Answers6

Linked