Remedy is a difficult and obscure mistress. I though this was hard to find via google. Basically, the Remedy Data Provider allows you to query its data. You can access the data directly in sql server, but the datatype for date fields are stored as integers. The integer represents the epoch time, or time since the first second of the year 1970(Unix Time). I am using C#.NET in this code sample. To get the value of the dates in .NET, you have to do a conversion. I have included code to go both ways.
public static DateTime NineteenSeventy = DateTime.Parse("1/1/1970");
public const int TimeZoneDifference = 4;
public static DateTime ConvertFromEpoch(int epochTime)
{
//Add time to start of epoch, and adjust for TimeZone
DateTime retDate = NineteenSeventy.AddSeconds(epochTime).AddHours(TimeZoneDifference);
return retDate;
}
public static int ConvertToEpoch(DateTime dateTime)
{
//Take time, adjust for Timezone, and return total seconds
DateTime timeZoneTime = dateTime.AddHours(-TimeZoneDifference);
TimeSpan temp = timeZoneTime.Subtract(NineteenSeventy);
return ((int)(temp.TotalSeconds));
}
For the SQL query translation you can use
{HD_YourTable.Create-date} in
Datediff("s", '1/1/1970', '8/30/2006 16:20:00') to Datediff("s", '1/1/1970', '8/30/2007'))
Stinkin' Remedy. Stinkin' Remedy data provider, not allowing joins....arg..