Javascript date to sql date object

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?

Answer

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!