TimeMatters date stored as int in SQL database

[ettienne]

I need to extract data from a TimeMatters 7 SQL database.
The date is stored in an int field, 05 Jan 2006 is stored as 74883.
Simple thinks I, it's Julian, but on closer inspection it turns out not Julian.
If I deduct 74883 from 01/05/2006 I get 12/28/1800 which is an odd starting date, but it is possible that some leap years are ignored in their calculation.
I could assume 12/28/1800 as a starting date, but I could be mistaken.
Are there any other date formats in use like Julian?
Or does anyone have insight into how TimeMatters saves their dates?

 

[Zathras]

I came across a similar thing in some software written by a Canadian consultant. I can't understand the logic either, but it is the number of days between the given date and 1/1/1753 plus 639906.

Whoever wrote TimeMatters 7 might have picked up the same function from them.

The Julian calendar was superseded by the Gregorian in 1582, but not fully adopted in the U.K. (and American colonies) until 1752. Perhaps the designer selected 1753 as the "base" year so that the DateDiff function would work correctly. But that still does not explain the offset of 639906.

I suppose one could call such a date a "Gregorian" date. I don't think calling it a "Julian" date is appropriate in any case.

 

[ettienne]

The programmers at TimeMatters said that it is stored as a Clarion date, as if that explains everything.
Does anyone know what a Clarion date is?

 

[ettienne]

I found the answer, Clarion day 1 is 12/28/1800, and the date is the number of days from day 1.

 

[gmmastros]

Is this a Microsoft SQL Server 2000 Database?

If it is... Here is my suggestion.

Create a User Defined Function to convert the dates for you. I generally advise against using UDF's because of performance issues. My rule for using a UDF is to hide complexity AND have re-usable code. If a function does not satisfy BOTH criteria, it should not be a UDF.

In this case, you will likely want to re-use the function from several places AND it hides the 'magic number' (12/28/18000).

To create a UDF...

Create Function ClarionToDate
	(
	@ClarionDay Integer
	)
Returns DateTime
As
Begin
	Return Convert(DateTime, '1800-12-28') + @ClarionDay
End

To use it...

Select dbo.ClarionToDate(74883)

or

Select ClarionDate,
       dbo.ClarionToDate(ClarionDate) As TheRealDate
From   TimeMatters7Table

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ettienne]

George,
Yes it is an MS SQL 2000 database.
Thanks for the suggestion. The guys at TimeMatters will have a hissy fit if I add anything to THEIR database, they are already hyper ventilating just because I am looking at it. They want me to sign a "if you screw it up you die" document, which I promptly told them to shove.

I am running a SQL query in VB6 and it is simple enough to get the date I want using:

SELECT DateAdd("d",[txhdr]![txndate],#12/28/1800#) AS TranDate
FROM txhdr;

I am attaching the txhdr to an Access database.
It might not be the best performance wise and so on, but the recordset is small so time is not an issue.

Ettienne