Clarion Date Conversion to SQL Server

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?
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)
Highlighted
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

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download