• RedDot CMS Blog
• 02.01.2017
• EN

rdb: How to convert OpenText CMS date values

There are several ways of converting a RedDot CMS date into a readable format. So here is another collection of solutions, based on comments in several programming languages like RQL, VB.NET, Javascript, PHP and more...

RedDot CMS date VB.NET solution by Kim

 VB.NET 
' 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

RedDot CMS date PHP solution by Matthias von Deetzen

 PHP 
$objIO->decodedate((string));

Javascript code to convert RedDot CMS date to YYYY-mm-dd, by Vitaliy Rabotnik

 Javascript 
function convertToDate(floatNum)
{
    var strOut = "";
    var days = ("" + floatNum);
    if(days == 0)
        return strOut;
    if(days.indexOf(""."") > 0)
        days = days.substring(0, days.indexOf("".""));
    days = parseInt(days, 10);
    var d = new Date();
    d.setFullYear(1899,11,30);
    d.setDate(d.getDate() + days);
    strOut = d.getFullYear() + ""-"";
    if((d.getMonth() + 1) < 10)
       strOut += "0";
    strOut += (d.getMonth() + 1) + "-";
    if(d.getDate() < 10)
       strOut += "0";
    strOut += d.getDate();
    return strOut;
}

And a Javascript snippet to convert RedDot CMS into a Javascript date object, by Sam and fixed by Jian

 Javascript 
// both Server and Client are on EST
var ajusted_milliseconds = 18000000; // 5 hours away from jan 1 1970 UTC
function ConvertToRedDotDate(MMDDYYYY)
{
  var DateTimeObj = new Date(MMDDYYYY);
  var RedDotDate = Math.floor((DateTimeObj.getTime() + ajusted_milliseconds) / 86400000) + 25569;
  return RedDotDate;
}

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 + ajusted_milliseconds);
  return RetDate;
}

C# .NET example taken from John Allens .NET RQL Wrapper (thanks Jian)

 C#.NET 
static void Main(string[] args)
{
    string StrOADate = "40228.7683217593";
    double OADate = System.Convert.ToDouble(StrOADate);
    DateTime DecodedOATime = DecodeFromOADate(OADate);
    double ConvertedOADate = EncodeToOADate(DecodedOATime);
    Console.WriteLine("Original OADate: " + OADate);
    Console.WriteLine("Regular Date Time: " + DecodedOATime);
    Console.WriteLine("Converted OADate: " + ConvertedOADate);
    Console.Read();
}

static DateTime DecodeFromOADate(double OADate)
{
    return DateTime.FromOADate(OADate);
}

static double EncodeToOADate(DateTime RegDate)
{
    return RegDate.ToOADate();
}

Java example by Frank Leja

 Java 
// Want to add the conversion routines I use within the jRQL class ReddotDate.
// see http://jrql.110mb.com/com/hlcl/rql/as/ReddotDate.html 
 // Wandelt man den 1970-01-01 00:00:00 in ein Microsoft Float Datum erhält
 // man 25569
 private final static double cFirstMSDayInDouble = 25569d;
 // nach Java ist 1970-01-01 00:00:00 die nullte Millisekunde
 // Microsoft hat hier aber schon die 2209161600000 Millisekunde
 private final static double cFirstMSDayInMilliSec = 2209161600000d;
 // eine natürliche Konstante, die Millisekunden eines Tages
 private final static double cMSecondsPerDay = 86400000d;
// ==>RedDot double value to Java milliseconds
 /**
  * ReddotDate constructor comment.
  *
  * @ param msDate
  *            a timestamp in the Microsoft format used by RedDot
  */
 public ReddotDate(double msDate) {
  super(Math.round((msDate - cFirstMSDayInDouble) * cMSecondsPerDay));
 }
// ==>Java date to RedDot double value
 /**
  * Liefert das Datum konvertiert in das von RedDot genutzte Microsoft float Format.
  */
 public double toMsDouble() {
  /*
   * // for day round to values without fraction final double ten =
   * 10000000000d; double time = new Long(getTime()).doubleValue();
   * //return Math.round((time + cFirstMSDayInMilliSec) / cMSecondsPerDay *
   * ten) / ten; return Math.round((time + cFirstMSDayInMilliSec) /
   * cMSecondsPerDay);
   */
  BigDecimal bd = new BigDecimal((getTime() + cFirstMSDayInMilliSec) / cMSecondsPerDay);
  return bd.setScale(10, BigDecimal.ROUND_CEILING).doubleValue();
 }

If you have got examples, feel free to share them below and I will update the article :)