One Star

Clarion Date Conversion to SQL Server

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

Re: Clarion Date Conversion to SQL Server

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

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Clarion Date Conversion to SQL Server

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)
One Star

Re: Clarion Date Conversion to SQL Server

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
One Star

Re: Clarion Date Conversion to SQL Server

yes
One Star

Re: Clarion Date Conversion to SQL Server

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

Re: Clarion Date Conversion to SQL Server

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

Re: Clarion Date Conversion to SQL Server

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

Re: Clarion Date Conversion to SQL Server

add 36161 to clarion date