javascriptdate-conversionexcel-dates

Convert SQL date number into date using javascript


Gabriele Petrioli please read my question before you close it.

my reports in SQL are daily, and INT as Date is perfect, two years ago I test Azure CosmosDB and I was able to convert by using JavaScript I did it by using UDF I just needed now for a project and bad luck, can't believe so many people are involved to downplay questions and administrating without programming skills and "managing", I will do it again I will not give up but I need the code today. I will post the solution if the managers did not close my question again.

here the original question:

I need to convert INT date produced by SQL into a Date using javascript.

I know this is possible I did it before I have a terrible day today and the time is clicking.

Here is the process: I convert the date to INT using SQL

select 'cast(getdate() as  int)'
select cast(getdate()  as  int)
-- today 43811

I need to bring it back using Javascript. No Jquery or else.

--more exaples
select 'cast(40702 as  smalldatetime)'
select cast(40702 as  smalldatetime)
--output 2011-06-10 00:00:00


select 'cast(getdate() as  float)'
select cast(getdate()  as  float)
-- output 43810.6597960262

Again I need to use JavaScript
convert2date(40702) --> 2011-06-10

convert2date(43811) --> 2019-12-14


Solution

  • What you have is a day count from 1900-01-01 and you need to add the given days.

    function convert2date(days) {
        const
            isLeapYear = year => (!(year % 4) && !!(year % 100)) || !(year % 400),
            daysInMonth = (month, year) => [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31][month - 1] + isLeapYear(year);
    
    
        var date = [1900, 1, 1],
            y = isLeapYear(date[0]) + 365,
            d;
        
        while (days >= y) {
            date[0]++;
            days -= y;
            y = isLeapYear(date[0]) + 365;
        }
        
        d = daysInMonth(date[1], date[0]);
        while (days >= d) {
            date[1]++;
            days -= d;
            d = daysInMonth(date[1], date[0]);
        }
        date[2] += days;
        
        return date.map((v, i) => v.toString().padStart(i ? 2 : 4, 0)).join('-');
    }
    
    console.log(convert2date(40702)); // 2011-06-10
    console.log(convert2date(43811)); // 2019-12-14