0

SQL Server can convert a date to a float. For instance '2019-05-23 17:33:16.780' will be 43606.731444213

If I am not mistaken:

  • the integer is the number of days past since epoch (beginning of 1900/01/01)
  • the decimal part is the percentage of the day past since midnight.

Is there an easy way to do the same conversion in javascript ? I do not want the float number to start from JS epoch.

Sylvain Gantois
  • 779
  • 1
  • 12
  • 28
  • 1
    I'm deleting my answer after reading the rest of your question. – BlueWater86 May 23 '19 at 05:46
  • "For instance '2019-05-23 17:33:16.780' will be 43606.731444213". Not for everyone. SQL Sever converts the value to UTC, whereas 2019-05-23 17:33:16.780 is local, so represents a different UTC time in each timezone with a different offset. – RobG May 23 '19 at 09:37

1 Answers1

1

You can use the JavaScript Date prototype and define your own initial date:

var date = new Date('2019-05-23 17:33:16.780');
var init = new Date('1900-01-01');

console.log((date - init) / (1000 * 60 * 60 * 24));
// 43606.68977754629

The division by 1000 * 60 * 60 * 24 is to convert from milliseconds to days.

Alternatively, you can use the moment.js library:

var date = moment('2019-05-23 17:33:16.780');
var init = moment('1900-01-01');

console.log(date.diff(init) / (1000 * 60 * 60 * 24));
// 43606.69922199074
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment.js"></script>
DjaouadNM
  • 22,013
  • 4
  • 33
  • 55
  • 2
    In case you also need to handle daylight saving time, this post should be useful: https://stackoverflow.com/a/11252167/1462234 – Daniel Z. May 23 '19 at 05:56
  • The moment library gives the right value but we need to take daylight saving time into account as mentionned by @DanielZ. The first method is slightly wrong. Marked as answer. – Sylvain Gantois May 23 '19 at 06:24
  • `new Date('2019-05-23 17:33:16.780')` is a bad way to create a Date. Parsing non–standard strings with the built–in parser is implementation dependent and returns an invalid date in at least one current host. If parsed correctly, it will be treated as local, whereas `new Date('1900-01-01')` will be treated as UTC, so you've introduced an error equivalent to the host timezone offset (which might be +14 hours to -10 hours). – RobG May 23 '19 at 09:31
  • @RobG It was only for demo purpose, basically to show how to do get the difference between any two dates in days. How would you suggest creating a date from a string? – DjaouadNM May 23 '19 at 11:34
  • There are 3 formats supported by ECMA-262, however general advice is to avoid the built–in parser. – RobG May 23 '19 at 19:55
  • @RobG What about the `moment.js` parser? It's supposed to solve these kinds of problems I guess? – DjaouadNM May 23 '19 at 21:46
  • No it doesn't. If not told the format, it tests for ISO 8601, then RFC 2822, then just uses the built–in parser. Moment.js parses "2019-05-23 17:33:16.780" as an ISO 8601 format. However, it's allowed by ISO 8601 only by agreement between the parties exchanging the string, it should not be assumed that the omitted "T" will be tolerated. ECMA-262 does not allow the "T" to be omitted, so Safari treats it as a non–compliant string (which ECMA-262 allows). – RobG May 24 '19 at 05:35