CMS

How to convert OpenText CMS date values

There are several ways of converting a RedDot CMS date into a readable format.
Kim has previously posted solutions for VB here. 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

' 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

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

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

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

// 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)

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

// 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

Related posts:

1. RQL development with OpenText Web Site Management Server 11
2. Clear OpenText Web Site Management CMS Cache
3. Workaround – Placing red dots inside of OpenText CMS Render Tags

About the author:

Markus Giesen is a Solutions Architect and RedDot CMS Consultant, formerly based in Germany. Travelling around the world to find and offer solutions for a better world (in a very web based meaning). He just found a way to do this as part of a Melbourne based online consultant house. On this blog Markus shares his personal (not his employers) thoughts and opinions on CMS and web development. In his spare time you will find him reading, snowboarding or travelling. Also, you should follow him on Twitter!

Tags: code snippets, Javascript, OpenText Web Content Management, php, RedDot CMS Date, Tricks, VB.NET