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
Two Stars

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?
Two Stars

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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now