How does Navision store time in SQL Server?

gaya3hari · 2005-01-04

Hi,
I am very new to Navision. I am part of an integration team which is migrating data to and from navision to another database. My Navision setup is based on SQL Server. There is a column called "shipping time" in sales header. When I try to enter some data into that column through Navision, say "CD", the data getting stored in SQL Server is " ".. What does this exactly mean? Can any of you help on this context?

Thanks.

kine · 2005-01-04

If you look to field type, you can see that this field is DateTime. If in Navision it is Time type, the date part is set to "1.1.1754". If it is Date type in Navision, the Date part is the date and time is 0:0:0.

BUT in some cases there are fields with name "... Time..." but are defined as Decimal or Integer in Navision. In this case it is some number representing the time in no. of ms or seconds sience midnight.

gaya3hari · 2005-01-04

Thanks but in my case, Shipping time is "date formula" in Navision and stored as "varchar(32)" in SQL Server.. So, what's the problem in displaying the date/time as it is...

fko · 2005-01-04

DateFormula is not Date and not Time, it's some formula to count some date/time.

kine · 2005-01-04

Because the value must be saved language neutral, it cannot use the characters. I looked into the data and see that each char (D,M,C etc.) is replaced with character with ASCII value 01, 02, 03 etc.

For example:

C = 01
D = 02
M = 05

in this case the '+1D-CM' will be saved as '+1{02}-{01}{05}'

where {xx} is char with ASCII xx. You must replace the characters back (if you use unicode the char code will be 00 01 etc...

gaya3hari · 2005-01-04

ok..from a book i read..
"Date Formula internally is handled as an
information that will be stored independently of language settings."

but, how will a SQL Server user interpret that data..
Here, when I do a SELECT [SHipping Time] FROM ...$Sales Header..
I get ""..what does this mean? As i had already said , I need to migrate this data into some other db..and so it is essential that i read this data properly..

kine · 2005-01-04

Fill one record with all possible characters ('CD+1M+2Y+3W+4Q') and you will see the data:

'{01}{02}+1{05}+2{07}+3{04}+4{06}'

C (current) = 01
D (day) = 02
M (month) = 05
Y (year) = 07
W (week) = 04
Q (quarter) = 06

Do replace and it is OK...

gaya3hari · 2005-01-04

Yes, I tried giving CD+1M+2Y+3W+4Q in Shipping time in Navision, but.. I am still getting "" when i run the query from SQL Server.. I think you are able to understand that I am trying to run the query from SQL Server and see whether I am getting the data which I have entered in Navision...

kine · 2005-01-04

Yes, but you must format the data on output by replacing the characters. Without any format you have data which are saved. But Navision when read the data, make replacement and than shows the data. You must do same thing... Without any formating or changing only with raw select you will see " instead the characters...

gaya3hari · 2005-01-04

Hi,
I did this:

select replace([shipping time],'C','01')
from [companyname$Customer]

but..nope.. i am still getting .. am i on the right track?

kine · 2005-01-04

no string '01' but character with ASCII value = 1 ... But I don't know how to write this characters in SQL...

gaya3hari · 2005-01-04

Hurray!

I got the result by giving this..
select replace(replace([shipping time],CHAR(1),'C'),CHAR(2),'D'), *
from [companyname$Customer]

actually, CHAR(1) will give you the character corresponding to ascii value 1..

Thanks a ton for your help!, Kine

kine · 2005-01-04

I am glad that I was able to help you... \:D/

kfons · 2012-10-03

I am having the same issue here, I tried to do the code as stated but it's not really working. I get either 0D or blank for everything. I'm basically trying to jsut display what is in the Safety Lead time field on the item card in a SSRS report. No calcualtions, etc.
We use the field for shipping leadtime on overseas items and I am trying to do a report of items purchased from overseas that do not have a value in Safety leadtime.
Any suggestions?

Kevin

kine · 2012-10-04

Than problem will be somewhere else. Check that you are working with correct db and correct table and field.

Howdy, Stranger!

Quick Links

Categories

How does Navision store time in SQL Server?

Comments