As soon as I posted this, I discovered I could successfully use a
syntax like this:
SQL|SELECT CAST (LastScanDate / 1000 as int) FROM <tablename>
On May 19, 4:41 pm, googerb <[EMAIL PROTECTED]> wrote:
> Has anyone been able to import a timestamp from an external database
> using AIE data mappings, where the souce data is an a millisecond unix
> timestamp format.
>
> I can execute SQL statements directly in the source database to
> maniuplate the data, but AIE will not import the data due to it being
> in milliseconds. Any syntax I've tried to divide the data by 1000 to
> convert the data into seconds is rejected by AIE.
>
> In every case, the AIE syntax ignores the "/ 1000" syntax and tries to
> import the full timestamp in milliseconds and I recieve this error:
> "*** ERROR *** The conversion of the nvarchar value '1210689723000'
> overflowed an int column. Maximum integer value exceeded. (SQL Server
> 248)"
>
> Source data example:
>
> E.g., "1210689723000" for Tue, 13 May 2008 14:42:03.
>
> AIE data mapping syntax that works with a direct SQL statement but
> does not work with AIE:
>
> SQL|SELECT CAST('$LastScanDate$' / 1000 as int)
>
> SQL|SELECT convert(datetime,DATEADD(s, ('$LastScanDate$' / 1000), '1
> January 1970')
>
> SQL|SELECT CAST (Dateadd(s, (CAST(LastScanDate / 1000 as int)), '1
> January 1970') as datetime)
>
> Can anyone help? Thanks!
>
> _______________________________________________________________________________
> UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org
> Platinum Sponsor:www.rmsportal.comARSlist: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
