Date / Time Stamp displayed differently in SQL vs. NAV

rsaritzky

Hi:

I have a "staging" table where I have an outside process write records into this table. The table is then processed by a NAV report to import some data.

I am in SQL Management Studio and I run the following command:

UPDATE [StagingTable] SET [ImportDateTime] = '09/23/2009'

Then, if I display the records in SQL Mgt Studio, it shows the [ImportDateTime] as "2009-09-23 00:00:00.000" - just as I would expect it.

However, if I go into NAV and display the same table, the ImportDateTime field shows as "09/22/09 05:00:00PM"

Now, I know what the difference is. We are in Los Angeles, so we are Universal Time (UTC) minus 7 hours, and our servers are aware of that.

So, one of my questions is how can I, in native SQL, tell my update statement to update the table with the local time instead of the UTC time? Even if I use a SQL function such as "current_timestamp", the same thing occurs. Will it be necessary to do UCT conversion to get the two times to display the same?

Thx

Ron

rsaritzky

I'm sorry - I forgot to specify my environment:

NAV 3.60 database under NAV 5.0SP1 client
SQL 2005

Ron

kriki

The reason is that a datetime in SQL is saved as a universal time that also includes the timezone.
To test it out what you must save into the field, Create a record using NAV and then check in SQL what was written.

rsaritzky

kriki wrote:

The reason is that a datetime in SQL is saved as a universal time that also includes the timezone.
To test it out what you must save into the field, Create a record using NAV and then check in SQL what was written.

I know that a when date in NAV is entered, it is stored as the UTC value - that is not my primary concern.

My question is, I want to insert a date using code outside of NAV but have the same date/time value as when I insert a value using NAV. For example, if the current date and time is 9/23/09 11:55am I write CAL code in a NAV object:

"Transaction Date" := CURRENTDATETIME

Then if I look at the value of the field in NAV it will show 9/23/09 11:55am. If I look at the value of the field in SQL Mgt Studio it shows 9/23/09 4:55am.

However, if I update the value in SQL

Update <tablename> SET [Transaction Date] = current_timestamp

Then if I look in NAV, the value of the field is 9/23/09 8:55pm and the value in SQL is 9/23/09 11:55am.

This applies even if I use a manually entered date, e.g. in CAL if I say "Transaction Date" := CREATEDATETIME(092309D,1155am) it stores it the same as above.

Ron

mboon75

Hi Ron

DATETIME format in NAV is to my opinion useless. I walked into the same problem as you do.
The following is from the help of NAV on DATETIME. In daylight saving period your dates display one hour different. I fixed it by storing the date in as a biginteger and calculated from 1/1/1970T0:00:00

Hope this helps.

Regards

Mark

Note

The biannual change to and from daylight saving time affects the way in which datetimes are displayed. For example, if you are located in central Europe, you are in the central European time zone and are using Central European Time (CET). During the winter the difference between CET and UTC is 1 hour. When you change to daylight saving time for the summer the difference is 2 hours.

This means that the local time representation of a UTC will vary depending on the time zone you are located in and whether you are using daylight saving time or not. Therefore, any datetimes that were entered during the winter will be converted and displayed differently after you have changed to daylight saving time for the summer. In other words, datetimes will not necessarily be displayed the same as when they were entered.

The DateTime datatype does not support closing dates.

By default, datetimes are displayed using the standard display format. When you use the standard display format, seconds and milliseconds are not displayed until you select the datetime field. Furthermore, if you export your data using the standard dataport export, the User Portal dataport export or by writing it to a file, the seconds and milliseconds will not be exported unless you specify that datetime fields use another format and display this information. For more information about how datetimes are displayed and the formats that are available, see Format.

rsaritzky

Since the purpose of my SQL code is to log both the date and the time, I guess the conclusion is to:

a. Create separate date and time fields
b. Write the SQL code to convert the current date/time to local time before it inserts it.

Ron

kine

On SQL you need to use GETDATE or GETDATEUTC (I am not sure which is correct one, just try). One is local time, one is UTC time... ;-)

rsaritzky

kine wrote:

On SQL you need to use GETDATE or GETDATEUTC (I am not sure which is correct one, just try). One is local time, one is UTC time... ;-)

GetDate works - it will display the same time in SQL and NAV, e.g.
UPDATE [StagingTable] SET [ImportDateTime] = GETDATE()

But if I want to insert a SPECIFIC date in SQL, it appears I have to use a calculation to take that date date and add 7 hours, e.g.
UPDATE [StagingTable] SET [ImportDateTime] = dateadd(hour,7,'09/01/2009')

If I do this, inside NAV, the date field will display as '09/01/09 12:00am' instead of '8/31/09 5:00PM'.

kine

look there: http://msdn.microsoft.com/en-us/library/ms180878.aspx

Mainly the part about ISO 8601 Format:
# YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]
# YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC, Coordinated Universal Time)

You can try to enter the date and time as

UPDATE [StagingTable] SET [ImportDateTime] = dateadd(hour,7,'2009-01-09T17:00Z') for UTC time
UPDATE [StagingTable] SET [ImportDateTime] = dateadd(hour,7,'2009-01-09T17:00-9:00') for Timezone with -9 hours