topic Re: Clarion Date Conversion to SQL Server in Talend Studio

Clarion Date Conversion to SQL Server

Anonymous — Sat, 16 Nov 2024 14:29:23 GMT

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

Re: Clarion Date Conversion to SQL Server

Anonymous — Mon, 17 Dec 2007 02:32:16 GMT

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

shong

Re: Clarion Date Conversion to SQL Server

Anonymous — Wed, 19 Dec 2007 22:18:02 GMT

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)

Re: Clarion Date Conversion to SQL Server

c0utta — Wed, 19 Dec 2007 23:44:32 GMT

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

Re: Clarion Date Conversion to SQL Server

Anonymous — Thu, 20 Dec 2007 00:14:08 GMT

yes

Re: Clarion Date Conversion to SQL Server

Anonymous — Thu, 20 Dec 2007 00:15:39 GMT

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

Re: Clarion Date Conversion to SQL Server

c0utta — Thu, 20 Dec 2007 00:48:39 GMT

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

Re: Clarion Date Conversion to SQL Server

Anonymous — Thu, 20 Dec 2007 06:44:52 GMT

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

Re: Clarion Date Conversion to SQL Server

_AnonymousUser — Fri, 14 Oct 2011 14:25:05 GMT

add 36161 to clarion date