Pages

Wednesday, September 10, 2014

RedDot CMS Date Conversion in MS 11

Since COM and DCOM is going away in Management Server 11, the following RedDot date conversion code using "RDCMSAsp.RDPageData" may not work.

ASP (From RQL Manual)
...
' From an RQL assign a value to a variable
OldDate = EinObjekt("changedate")

' Convert value by calling the DecodeDate function
' and output in both variants.
response.write "Date from attribute=" & OldDate & " --> Convert date=" & DecodeDate(OldDate)
...

Function DecodeDate(OldDate)
    Dim objIO As Object                              ' Define object
    Set objIO = CreateObject("RDCMSAsp.RDPageData")  ' Create object
    DecodeDate = objIO.decodedate(OldDate)           ' Convert number into date
End Function

Here are the new code that should work in all versions.

ASP
Function ConvertToRedDotDate(DateObject)
    ConvertToRedDotDate = DateDiff("d","12/30/1899", DateObject)
End Function

Function ConvertFromRedDotDate(RedDotDate)
    Dim DateObj, Int_Days
    DateObj = "12/30/1899"
    Int_Days = Int(RedDotDate)
    DateObj = DateAdd("d",RedDotDate,DateObj)
    DateObj = DateAdd("s",(RedDotDate-Int_Days)*86400,DateObj)
    ConvertFromRedDotDate = DateObj
End Function

C#
DateTime DecodeFromOADate(double OADate)  
{  
    return DateTime.FromOADate(OADate);  
}  
  
double EncodeToOADate(DateTime RegDate)  
{  
    return RegDate.ToOADate();  
}  

JavaScript
function ConvertToRedDotDate(DateObj)
{
    // day in milliseconds
    var DAY_IN_MILLISECONDS = 1000 * 60 * 60 * 24;
    var BEGINING_DATE_MIllISECONDS = new Date(1899,11,30).getTime();

    var DateObj_Milliseconds = DateObj.getTime();

    return Math.round(Math.abs(DateObj_Milliseconds - BEGINING_DATE_MIllISECONDS)/DAY_IN_MILLISECONDS);
}

function ConvertFromRedDotDate(ReddotDate)
{  
    var days = Math.floor(ReddotDate);
    var milliseconds = Math.round((ReddotDate-days)*86400000);
    var adjusted_days_in_millseconds = (days-25569) * 86400000;
    var RetDate = new Date();
    RetDate.setTime(adjusted_days_in_millseconds + milliseconds);
    var ajusted_minutes = RetDate.getTimezoneOffset();
    RetDate.setMinutes(RetDate.getMinutes() + ajusted_minutes);
 
    return RetDate;  
}

2 comments:

  1. Great post, Jian. I also found if getting raw results from SQL, a CONVERT(DateTime, [Column Name] -2) accounts for OLE's 12/30/1899 and SQL's 1/1/1900 date offset.

    ReplyDelete
  2. Thanks to a bug discovery by Andy Salik. The JavaScript ConvertToRedDotDate() function is now converting the date correctly.

    ReplyDelete