Time DataType

RS8
RS8 Member Posts: 121
edited 2009-05-13 in NAV/Navision Classic Client
Hi,

I have inserted data into Sales Header table field "Time of Removal" thru SQL, the SQl has only datetime datatype, so it stores as "Date & Time" both date and time. After that when i open Sales Order i get an error message as

The Sales Header table contains a Time field that uses invalid data for the date component of the DATETIME value:

Field: Time of Removal
Value : 1900-01-01 13:45:44.000


can any one suggest how to insert only time into Navision through SQL Query.

Comments

  • kapamarou
    kapamarou Member Posts: 1,152
    In SQL time is stored as
    1/1/1754 HH:MM:SS (that's shown using my local settings for time)

    The important thing is to have the year as date part as 01/01/1754 which I think is the lower boundary for SQL dates.
  • manuv
    manuv Member Posts: 5
    You can try the following example on a Navision (ver 5) table

    INSERT INTO [NavitionERP].[dbo].[StageInputTest]
    ([Name]
    ,[LogDate]
    ,[LogTime]
    ,[age])
    VALUES
    ('Mike',
    '2009-05-13 00:00:00.000',
    cast('1/1/1754 ' + convert(varchar, cast('2009-05-13 03:04:00 PM' as datetime),108) as datetime) ,
    12)

    here I need to replace the date portion of the current-system-time into '1/1/1754 ' and combine the time portion to make the Navition time format to insert the time value into the LogTime(Time DataType in Navision) field.

Categories