NAV Table reading a SQL View

MarkD33

We are experiencing a really odd situation with a SQL View on an intergration project.

Here's the background.

Billing records are written to in interface table in SQL. There's a view in the NAV database that reads the interface table. Then, there's a table in NAV that reads the SQL view. A report runs in NAV that reads the NAV table of the SQL view to pull data from the interface table to create Sales Invoices.

The process runs great, however, there's something weird occurring with one of the dates the client needs on the invoice. When the sales invoice is created, the date is consistently one day less than the date in the source interface table.

When I open up the interface table in SQL and look at the date column, the date = 6/21/07 12:00:00 AM

When I open up the view of the interface table in SQL and look at the date column, the date = 6/21/07 12:00:00 AM

When I open up the NAV table of the SQL view and look at the date column, the date = 6/20/07 7:00:00 PM.

For some very strange reason, the NAV table consistently subtracts 5 hours from the date in the SQL view. Thus, when the import runs, the date is off on the invoices by one day.

Any thoughts on why this could be occurring? I checked system times on the client's servers thinking that maybe an incorrect time zone could be causing the problem, but that's not the case.

Mark

ara3n

Hello
Navision stores datetime on sql at UTC time. I would suggest changing the View and add something to change the date to UTC. I believe there is a getUTC function that will do the translation.