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?

All SQL databases can read the ISO date format unambiguously: ("YYYY-MM-DD HH:mm:ss") firstDayOfMonth.toISOString() should do the trick. — Curt
– Curt, Commented Nov 19, 2013 at 22:51
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
– James, Commented Nov 19, 2013 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: momentjs.com/docs/#/parsing/asp-net-json-date — guiomie
– guiomie, Commented Nov 19, 2013 at 22:54
@James I'm using the atmosphere library and javax.jws server side to handle the web services. — Graham
– Graham, Commented Nov 19, 2013 at 22:57
@guiomie That's not an option. This query string being run from an app created with Sencha. — Graham
– Graham, Commented Nov 19, 2013 at 22:59

Mark Skelton · Accepted Answer · 2021-01-05 03:05:00Z

72

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 5, 2021 at 3:05

Mark Skelton

3,9114 gold badges31 silver badges51 bronze badges

answered Jan 31, 2014 at 14:30

JPK

1,3722 gold badges14 silver badges25 bronze badges

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

5 Comments

Justin Over a year ago

toISOString support is IE9+ kangax.github.io/compat-table/es5/#Date.prototype.toISOString

Tom Stickel Over a year ago

question was on MS SQL ( T-sql) not mysql :/

Jonas Rotilli Over a year ago

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

maxime schoeni Over a year ago

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

CaNNaDaRk May 18 at 18:57

As stated by Maxime up here, using "toSOString" will first convert your date into UTC so, in case you are using a different timezone in the database connection, the result might represent a wrong time. In other words, this is reliable only if you assume Node the database connection is UTC too. In my case, new Date().toISOString().slice(0, 19).replace('T', ' '); results in 2025-05-18 18:57:01 which is UTC, but then database expects CET so the result is incorrect.

Corylus · Accepted Answer · 2021-08-18 05:32:12Z

7

using MomentJs it will be pretty easy.

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

https://momentjs.com/

edited Aug 18, 2021 at 5:32

Corylus

9378 silver badges17 bronze badges

answered Dec 4, 2020 at 8:54

Jithil P Ponnan

1,25712 silver badges12 bronze badges

Comments

Arjun G · Accepted Answer · 2022-12-21 21:07:16Z

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, 2022 at 21:07

Arjun G

2,3241 gold badge22 silver badges20 bronze badges

answered Sep 13, 2021 at 16:58

Hashir Qadeer

511 silver badge5 bronze badges

1 Comment

jean Over a year ago

There's already a answer with this exact snipet

nanobar · Accepted Answer · 2023-03-01 13:01:17Z

3

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 1, 2023 at 13:01

nanobar

66.8k26 gold badges155 silver badges178 bronze badges

Comments

Simon Borsky · Accepted Answer · 2022-03-10 13:06:01Z

2

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

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

Enjoy!

edited Mar 10, 2022 at 13:06

answered Sep 1, 2020 at 11:08

Simon Borsky

5,1812 gold badges25 silver badges21 bronze badges

1 Comment

Mehrdad Nazmdar Over a year ago

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

maxime schoeni · Accepted Answer · 2023-12-18 09:55:30Z

2

Hopefully there is one country in the world that is sql friendly:

console.log(new Date().toLocaleString('lt-LT'));
// 2023-12-18 10:48:55

Hurray for Lithuania!

answered Dec 18, 2023 at 9:55

maxime schoeni

2,9072 gold badges22 silver badges21 bronze badges

1 Comment

CaNNaDaRk May 18 at 18:46

This should be the right answer or at least this should be considered as a possible solution.

pedro.caicedo.dev · Accepted Answer · 2022-09-02 19:57:04Z

1

I use:

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

answered Sep 2, 2022 at 19:57

pedro.caicedo.dev

2,4952 gold badges18 silver badges19 bronze badges

2 Comments

timkay Over a year ago

I think new Date().toISOString().replace(/T/, ' ').replace(/Z/, '') would be more efficient.

pedro.caicedo.dev Over a year ago

Thanks @timkay, i'll try it the next time :+1

BDorries · Accepted Answer · 2024-04-20 02:32:38Z

1

Here is the solution I came up with by combining several of the above answers.

const currentDate = new Date().toLocaleString('lt-LT').substring(0,10); this gives an output of YYYY-MM-DD HH:MM:SS, so I made a substring to cut off time which makes it comparable to sql date. then I used a simple greater than operator to compare the dates. For my use case, I needed to see if the sql date was today or later so I did this

events = //events array from db
const currentDate = new Date().toLocaleString('lt-LT').substring(0,10);
events = events.filter((event) => event.date >= currentDate)

This gave me an array of events that were either today or in the future.

answered Apr 20, 2024 at 2:32

BDorries

215 bronze badges

2 Comments

Abra Over a year ago

Pardon me, but I don't understand how your answer is related to the question. The question asks how to convert JavaScript Date to a string that can be converted by both Oracle and SQL Server databases to a value having the DATE data-type. How is an array of events that were either today or in the future related? Also, isn't 'lt-LT' the locale for Lithuania? How is that related to the question?

BDorries Over a year ago

@Abra I used the Lithuania locale because it outputs a string that very closely resembles a sql date. I mentioned that rationale in my answer. While my particular use case for needing the string wasn't strictly related to the OPs question, I included it to demonstrate a possible use for that date string. My code snippet creates a timestamp that needs very little change to make it comparable to the sql time format

Collectives™ on Stack Overflow

Javascript date to sql date object

8 Answers 8

5 Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

5 Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related