Wednesday, September 16, 2015

[JavaScript] Converting JavaScript DateTime to SQLServer DateTime

I've had the need occasionally to convert JavaScript datetime to SQLServer datetime numeric Epoch value. The following two routines do the job. In both cases I'm assuming that UTC is being used as taking timezones into consideration is a bit more work.

First jsDateTimeToSQLServerDate which takes a JavaScript Date object and forms a SQLServer Epoch integer.

   function jsDateTimeToSQLServerDate(d) {

      var SQLServerEpoch = 2208988800;
      var dMilli = d.valueOf();
      var dSec = dMilli / 1000;
      var nEpoch = SQLServerEpoch + dSec;
      var nEpochDays = nEpoch / 86400;
      var nEpochDate = Math.ceil(nEpochDays);
      return nEpochDate;
    }
The second also takes a JavaScript Date object but this time includes the time returning an Epoch floating point number.
   function jsDateTimeToSQLServerDateTime(d) {
      var SQLServerEpoch = 2208988800;
      var dMilli = d.valueOf();
      var dSec = dMilli / 1000;
      var nEpoch = SQLServerEpoch + dSec;
      var nEpochDays = nEpoch / 86400;
      var nEpochDate = Math.ceil(nEpochDays);
      return nEpochDays;
    }
The Closure compiler abbreviates those routines signficantly, as below. You may want to use this code in preference as the above was done to demonstrate the relationships in the conversion process.
   function jsDateTimeToSQLServerDate(a) {
      return Math.ceil((2208988800 + a.valueOf() / 1E3) / 86400);
    }
    function jsDateTimeToSQLServerDateTime(a) {
      a = (2208988800 + a.valueOf() / 1E3) / 86400;
      Math.ceil(a);
      return a;
    }
    ;
An example invocation:
   var now = new Date();
    // now currently
    // Wed Sep 16 22:52:09 UTC+0800 2015
    jsDateTimeToSQLServerDate(now);
    // gives
    // 42262
    jsDateTimeToSQLServerDateTime(now);
    // gives
    // 42261.61955061343
I hope this helps. I may be back here myself next time I need this routine.

© Copyright Bruce M. Axtens, 2015

2 comments:

Bere Farno said...

To get time zones in there would involve working out the offset from the name (harder with the .5 times zones admittedly). So it would be a switch on name. So tedious to write out but not a lot more work

Thank you for sharing

Bere Farno said...

It says " 1 comments " - you'd think blogger would try harder!