Reading SQL DateTime in Navision

mitrax

Hi,

i have a problem reading SQL Datetime in Navision.
My task is to select from another database (non-navision) recordsets and overtake them some of them into a navision table.
This works fine for varchar fields.
But there are two Datetime fields in the table that can not transferred. I get the error message, that this data type is not supported by C/SIDE.
I tried to transform the datetime field with sql-function DATEPART into its elements, but i had no success. Even if i only try to get one element of the datetime (e.g. year: DATEPART(yyyy,[datetime]) i still get the error message mentioned above.

Has anyone ever imported Datetime fields in navision and can give me a hint?

Thanks
Michael

bbrown

What version of NAV? Is it one of the older version that do not support DateTime as a datatype? If so, what are the field datatypes?

You may need to use CAST/CONVERT to translate DateTime into separate Date and Time fields.

mitrax

Oh sorry, i use version 5.0 SP1.

I paste some code where you can see what i do:

gaRecordset := gaADOConnection.Execute('SELECT [CPF_CNPJ_EMIT],convert(varchar(10),datepart(yyyy,[DT_AUT_SEFAZ]) as ThisRow FROM [NF-e].[dbo].[VW_CSF_RESP_NF_ERP]');
WHILE NOT gaRecordset.EOF DO BEGIN
    gaFields := gaRecordset.Fields;
    field := gaFields.Item('ThisRow');
    MESSAGE('%1', field.Value);
END;

Here i should get the year of the datetime field, but i get always the error message, that i mentioned in my first post.
It might seem a litte bit long-winded, but i tried to make it as elementar as possible.

Greetz
Michael

ara3n

In your sql statement use CONVERT or cast and change it to string.

This should solve your problem. I had same issue with decimal datatype and I casted to float and it resolved the issue.