24

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?

Graham
  • 5,102
  • 12
  • 52
  • 85
  • 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
  • 6
    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

4 Answers4

62

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!

Mark Skelton
  • 3,195
  • 4
  • 21
  • 43
JPK
  • 1,234
  • 2
  • 14
  • 25
6

using MomentJs it will be pretty easy.

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

https://momentjs.com/

Corylus
  • 465
  • 3
  • 11
jithil
  • 830
  • 8
  • 10
4

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', ' ');

Hashir Qadeer
  • 39
  • 4
3

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

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

Enjoy!

Simon Borsky
  • 4,305
  • 2
  • 19
  • 17