How to convert MSSQL datetime integer to YYYY/mm/DD format in JavaScript

Question

I get data from legacy API where there is a field 'opendate': 1086652416 I know that this is 24/06/2014

initially I have decide that this is unix_timestamp which should be multiply with 1000 and BINGO

Convert a Unix timestamp to time in JavaScript

var unix_timestamp = 1086652416;
var date = new Date(unix_timestamp*1000);
console.log(date); //  Date {Tue Jun 08 2004 06:53:36 GMT+0700 (NOVST)}

// As you see this is not 24/06/2014 !!!

99% that this is datetime format from MSSQL and it should be 24/06/2014

from early results: 1066467328 is 2011-12-15, 1066598400 is 2011-12-16

the question is: how could I convert this datetime to format YYYY-mm-dd in NodeJS

From MSSQL documenation:

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted.

What is your issue, precisely? You want to convert the ugly string Tue Jun 08 2004 06:53:36 GMT+0700 (NOVST) to something like 06/08/2004 or you want to have the correct date because SQL Server starts from 1900-1-1 and JS starts from 1970-1-1? Or both? — dgiugg, Jun 25 '14 at 11:47
I have an integer 1086652416 which I get from legacy API and I think it fetch from MSSQL The question how to convert it to Date in JavaScript? — Dilan, Jun 25 '14 at 12:03

score 0 · Answer 1 · answered Jun 25 '14 at 12:04

Just mocked this method in firebug, should demonstrate a simple example to format a js date.

    function formatDate(date){
        var df = "yyyy-MM-dd",
           tmp = date.getDate();

        df = df.replace("dd", tmp < 10 ? "0"+tmp : tmp);
        tmp = date.getMonth()+1; //Add 1 for humans
        df = df.replace("MM", tmp < 10 ? "0"+tmp : tmp);
        df = df.replace("yyyy", date.getFullYear());
        return df;
    }

    alert(formatDate(new Date()));

I need to get date from Integer like 1086652416 (as I know this is datetime in MSSQL) — Dilan, Jun 25 '14 at 12:07

score 0 · Answer 2 · answered Jun 25 '14 at 12:06

0

You are on the right track, you are correctly converting the unix timestamp to a javascript date.

Where you are wrong is that unix timestamp 1086652416 is NOT 24/06/2014. It is Mon, 07 Jun 2004 23:53:36 GMT

answered Jun 25 '14 at 12:06

mwarren

2,409
1
22
28

If you are using mssql presumably you should use the mssql convert method to convert the datetime to a british date string dd/MM/yyyy first (103 is for british date strings) CONVERT(VARCHAR(10),GETDATE(),103) – mwarren Jun 25 '14 at 13:08
There doesn't seem to be a way to convert directly to yyyy-mm-dd in mssql, which is pretty weird. – mwarren Jun 25 '14 at 13:14
the problem that I can NOT apply MSSQL's convert function coz this is not my API. And I do not want to setup MSSQl just for convertation – Dilan Jun 25 '14 at 13:56
This all seems a little unorthodox. You're getting Microsoft SQL Server stuff out without using mssql, which might get you into trouble as Microsoft programmes are notoriously proprietary. How exactly you are reading this data? – mwarren Jun 25 '14 at 14:56

score 0 · Answer 3 · answered Jun 25 '14 at 12:08

0

You should try this:

var date = new Date(1086652416 * 1000);
var commonTime = date.toLocaleString();
console.log(commonTime);

answered Jun 25 '14 at 12:08

Tyler.z.yang

2,402
1
18
31

this is 08.06.2004, 6:53:36 BUT I know 100% that this is 24/06/2014 – Dilan Jun 25 '14 at 12:37
Why you are so sure? Cause I transfer the unix time stamp in a tool. And the result is 08.06.2004 Could you give me your source data screenshot? – Tyler.z.yang Jun 25 '14 at 12:40
it's look that I have to install MSSQL and fetch it, what I'm currently trying to do – Dilan Jun 25 '14 at 13:05

How to convert MSSQL datetime integer to YYYY/mm/DD format in JavaScript

3 Answers3