Clarion Date Conversion to SQL Server

[Anonymous]

IS There away i can convert from Clarion Dates from the source database to SQL Server dates?

[Anonymous]

Hi
Can you show your Date format and what are your expected result?
Best regards

shong

[Anonymous]

My date format is this in a long: 72063 (this may look like either a clarion standard date or a julian date)
And I want to convert this into 4/17/98 (SQL Server Date Time format)

[c0utta]

Hi dantekcs,
I use JD Edwards which uses julian dates. I tried using a custom routine in Perl (my TOS language of choice) but found that it was easier to write a SQL function that returns the SQL date/time for comparison back into TOS.
If you don't want to go this way, then I believe you can use the Java dateFormat using a custom date parameter. My understanding is that Clarion dates are the day count since 28-Dec-1800, therefore 72063 is 17-Apr-1998. Is this correct?
Cheers,
c0utta

[Anonymous]

yes

[Anonymous]

is there away to call a sql function on insert of data?

[c0utta]

On tMSSQLOutput, Additional columns, SQL Expression = "dateadd(day, ?, '28-Dec-1800')", reference column = ClarionDate

[Anonymous]

correction for sql server 2005
"dateadd(day, CAST(? as INT), '28-Dec-1800')"

[_AnonymousUser]

add 36161 to clarion date