web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / SQL VS NAV TIME DIFFER...
Small and medium business | Business Central, N...
Suggested Answer

SQL VS NAV TIME DIFFERENCE

(0) ShareShare
ReportReport
Posted on by KrisMuthy 100

SQLvsNAVtimeDiff.bmpHi

I have nav database with BC spring 

Actually I am pulling some data from another database to NAV database using SQL query

It is showing correct date-time in SQL list but the same table in nav is showing different time

Please find attached document and advice

Warm Regards

Krishna

I have the same question (0)
  • Christian Mendoza Profile Picture
    Christian Mendoza 22 on at

    Hi

    As i mention before, "The reason is that a datetime in SQL is saved as a universal time that also includes the timezone. The date in NAV shows the datetime in your timezone."

    This is according to server configuration, but you can try do this:

    SELECT column_name,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), column_name)  AS ColumnInLocalTime from table1

    With this query, you can see the correct time (local)

    Christian Mendoza.

    Best regards.

  • KrisMuthy Profile Picture
    KrisMuthy 100 on at

    Ok but

    In SQL server the date and time ok.  

    when open nav table the data shows dfferent

    SELECT column_name,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), column_name)  AS ColumnInLocalTime from table1

    the above query good  and working fine

    but in NAV I am getting time is wrong

    Warm regards

    Kris

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    When you perform the INSERT into the NAV table, you need to convert the datetime value to UTC and save the UTC value to the NAV datetime field.

    You can do as follows in T-SQL to convert a datetime to UTC:

    SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 3,010

#2
Jainam M. Kothari Profile Picture

Jainam M. Kothari 1,270 Super User 2025 Season 2

#3
YUN ZHU Profile Picture

YUN ZHU 1,085 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans