Date time syntax for CE 10 query builder!!

[kostical]

I need to find out from the Admin launchpad query builder in Crystal Enterprise 10 which reports were scheduled to run on 1.31.07 but ran after midnight ie 2.1.07 or just the reports that ran on 2.1.07 (the report ran succesfully but just later than scheduled next day) I used this syntax
Select SI_LAST_RUN_TIME, SI_STARTTIME, SI_ID, SI_NAME, SI_PROGID FROM CI_INFOOBJECTS WHERE SI_LAST_RUN_TIME > '2007.1.31 11:59:59PM' I also tried this date time format '2007 01 31 11 59 59' that I saw in there knowledge base, didn't work!! I've been working on this for a while, called Crystal helpdesk and they are in the dark too! and will get back to me on it. Any tip will be appreciated!

 

[CarlGanz]

The section below is from my book on CE and BO SDK programming. Please let me know if it helps.

Thanks

Carl Ganz

Date and time values are stored as UTC—universal time—values, that is, Greenwich Mean Time. A report that is created at 9:30 p.m. on December 1, 2006, EST is stored in the SI_CREATION_TIME property as 2:30 a.m. on December 2, 2006, as Greenwich, England, is five hours ahead of
the eastern coast of the United States. To realign the dates and times, the SDK provides a UTCConverter class that will perform this translation for you. Listing 5-11 shows it in action.

Listing 5-11. UTC Converter Class

public string ConvertToUTC(string szUserID,
string szPassword,
string szSystem,
string szAuthentication,
string szServer,
DateTime dDateTime)
{
CrystalDecisions.Enterprise.Utils.UTCConverter oUTCConverter;
EnterpriseSession oEnterpriseSession = null;
string szResult;
oEnterpriseSession = GetIdentity(szUserID, szPassword, szSystem,
szAuthentication, szServer);
oUTCConverter = new CrystalDecisions.Enterprise.Utils.UTCConverter();
szResult = oUTCConverter.ConvertToUTC(dDateTime,
oEnterpriseSession.TimeZone);
return szResult;
}

The code in Listing 5-12 invokes the web service method that encapsulates this conversion
utility.

Listing 5-12. Converting Dates to UTC

localhost.CEWebService oCEWebService;
DateTime dDateFrom;
DateTime dDateTo;
string szDateFrom;
string szDateTo;
string szSQL;

dDateFrom = new DateTime(2006, 12, 1, 0, 0, 0);
dDateTo = new DateTime(2006, 12, 1, 23, 59, 59);

oCEWebService = new localhost.CEWebService();

oCEWebService.Credentials = System.Net.CredentialCache.DefaultCredentials;

szDateFrom = oCEWebService.ConvertToUTC("Dev", dDateFrom);
szDateTo = oCEWebService.ConvertToUTC("Dev", dDateTo);

szSQL = "SELECT SI_ID, SI_CREATION_TIME " +
"FROM CI_INFOOBJECTS " +
"WHERE SI_CREATION_TIME >= '" + szDateFrom + "' " +
"AND SI_CREATION_TIME <= '" + szDateTo + "'";

This code will produce a SQL string that looks something like this:

SELECT SI_ID, SI_CREATION_TIME
FROM CI_INFOOBJECTS
WHERE SI_CREATION_TIME >= '2006-12-01 05:00:00.000'
AND SI_CREATION_TIME <= '2006-12-02 04:59:59.000'