// you’re reading...

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

Share and Enjoy:
  • Print
  • email
  • Twitter
  • Digg
  • Reddit
  • StumbleUpon
  • Google Bookmarks
  • del.icio.us
  • MisterWong
  • Facebook
  • LinkedIn

No related posts.

About the author:

Markus Giesen 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!

Discussion

3 comments for “How to convert OpenText CMS date values”

  1. RustyLogic should get credit for an easier C# method

    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();
    }

    Posted by Jian Huang | January 28, 2011, 4:55 pm
  2. Thanks Jian, just updated the post!

    Posted by Markus Giesen | January 31, 2011, 4:51 am
  3. PHP-Function:
    function ConvertFromRedDotDate($reddotDate) {
    $reddotDate = str_replace(’,', ‘.’, $reddotDate);
    if (strpos($reddotDate, ‘.’) !== false) $days = substr($reddotDate, 0, strpos($reddotDate, ‘.’)); else $days = $reddotDate;
    if (strpos($reddotDate, ‘.’) !== false) $seconds = ‘0′.substr($reddotDate, strpos($reddotDate, ‘.’)); else $seconds = 0;
    $seconds = round($seconds * 86400);
    $adjusted_days_in_seconds = ($days – 25569) * 86400;
    if ($seconds != 0) {
    $result = gmdate(’d.m.Y H:i’, $adjusted_days_in_seconds + $seconds);
    }
    else {
    $result = gmdate(’d.m.Y’, $adjusted_days_in_seconds);
    }
    return $result;
    }

    MSSQL-Statement:
    SELECT DATEADD(second, (USR16 – CAST(USR16 AS int)) * 24 * 60 * 60, DATEADD(day, USR16, ‘1899-12-30′)) AS LastLogin, USR2, USR3
    FROM IO_USR
    ORDER BY LastLogin

    Posted by Michael Philipp | December 12, 2011, 12:40 pm

Post a comment



Stay up to date! - Get notified about followup comments

If you don't feel the urge to comment but wish to stay in the loop:
Just enter your email and subscribe to new comments.

Subscribe without commenting

Recent Tweets

  • RT @AirKraft: Transport Canada breakout: they manage 80K pages and 300K assets with WSM(RedDot). Wow! #OTCW 2010-11-11
  • The RedDot usergroup session 'Future of WCM' is in National Harbor 7, now. See you there! #otcw 2010-11-11
  • RT @yttergren: @AirKraft: Calling all WSM(RedDot) devs: share your solutions on http://bit.ly/bgPIof EVERY solution can win an iPad #OTCW 2010-11-10
  • Come to the Solution Exchange session. Enhance your (#reddot) CMS project! Chesapeake 12, 3:20pm #otcw Looking forward to see you there! 2010-11-10
  • More updates...