compare sql date to javascript date

Question

Is there an easy way to compare a sql dateTime to a javascript date time so that the two can be compared easily?

Are there built in javascript functions as I cant edit the sql

You should provide a bit more details like what are the technologies you are working with, how you are getting the date from SQL, is it through web service or some server side thing, etc. — Tariqulazam, Jul 03 '12 at 11:18
[take a look at this](http://stackoverflow.com/questions/7748205/compare-date-in-sql-statement) and proceed. — Jebin, Jul 03 '12 at 11:21

score 5 · Answer 1 · edited May 23 '17 at 11:45

Assuming that date string comes from SQL:

>>> new Date('2012-11-10 15:16:17')
Date {Invalid Date}

>>> new Date('2012-11-10T15:16:17')
Date {Sat Nov 10 2012 15:16:17 GMT+0100}

>>> new Date('2012-11-10 15:16:17'.replace(' ', 'T'))
Date {Sat Nov 10 2012 15:16:17 GMT+0100}

You can handle input from any timezone by appending the offset, like this for London time:

>>> new Date('2012-11-10 15:16:17'.replace(' ', 'T') + '+00:00')
Date {Sat Nov 10 2012 16:16:17 GMT+0100}

To compare two dates in JavaScript, simply subtract them to return the difference in milliseconds:

>>> var d1 = new Date('2012-11-10T15:16:17'); var d2 = new Date('2012-11-10T15:16:18'); d2 - d1
1000

Here's a nice class to compare by months and such.

score 2 · Accepted Answer · answered Jul 03 '12 at 11:36

2

To convert a MySQL DATETIME String into a JavaScript Date object:

        var sqlDateStr = "2012-01-02 23:58:59"; // as for MySQL DATETIME
        sqlDateStr = sqlDateStr.replace(/:| /g,"-");
        var YMDhms = sqlDateStr.split("-");
        var sqlDate = new Date();
        sqlDate.setFullYear(parseInt(YMDhms[0]), parseInt(YMDhms[1])-1,
                                                 parseInt(YMDhms[2]));
        sqlDate.setHours(parseInt(YMDhms[3]), parseInt(YMDhms[4]), 
                                              parseInt(YMDhms[5]), 0/*msValue*/);
        alert(sqlDate);

answered Jul 03 '12 at 11:36

Michael Besteck

2,415
18
10

it's working, just that in case the hours is something between 01 and 09, parseInt(YMDhms[3]) return 0 !! can you fix that ? – Feb 13 '13 at 11:02
Sorry, cannot reproduce your failure. If i, e.g. use the developer console of Opera32.0/Linux (Ctrl+I) then i get the following: – Michael Besteck Sep 28 '15 at 20:46
var sqlDateStr="2012-01-02 07:08:09"; sqlDateStr=sqlDateStr.replace(/:| /g,"-"); console.log(sqlDateStr); var YMDhms=sqlDateStr.split("-"); console.log(YMDhms); console.log("YMDhms[3] = "+parseInt(YMDhms[3])); 2012-01-02-07-08-09 ["2012", "01", "02", "07", "08", "09"] YMDhms[3] = 7 – Michael Besteck Sep 28 '15 at 20:53
[stackoverflow limits the nuimber of characters for an replying comment, so this comment consists of 3 parts] – Michael Besteck Sep 28 '15 at 20:57

score 1 · Answer 3 · answered Mar 21 '13 at 15:32

1

to fix parseInt(08) and parseInt(09), use parseInt(08,10) parseInt(09,10).

In Javascript numbers starting with zero are considered octal and there's no 08 or 09 in octal, hence the problem.

http://www.ventanazul.com/webzine/articles/issues-parseint-javascript

answered Mar 21 '13 at 15:32

user2195796

11
1

Not all browser interpret a leading zero (in input's) as an octal specifier. E.g. Opera 32.0/Linux (see my answer above) takes "0100" in INPUTs as decimal 64, but parseInt of "07" results in 7 decimal. – Michael Besteck Sep 28 '15 at 21:04
Also a parseInt("077") evaluates to 77 decimal. – Michael Besteck Sep 28 '15 at 21:11

score 0 · Answer 4 · answered Jul 03 '12 at 11:21

How are you accessing the sql datetime in javascript. Am assuming you have it as a string. The builtin js Date.parse() function can parse a variety of string and return a js Date object.

https://developer.mozilla.org/en/JavaScript/Reference/Global_Objects/Date/parse

If your sql date is being returned in a custom format, you will need to manually break it down into the relevant year, month, date, hours, min, second components and assemble the Date object using the appropriate Date() constructor.

compare sql date to javascript date

4 Answers4

Linked

Related