Format problem: MySQL datetime -> Navision datetime

Andre1977

Hello,
I am quite new in programming C/Side Navision. Currently I use Navision Attain 3.70 and I have the following problem:
I have a variable "value" containing a MySQL datetime (Format: YYYY-MM-DD hh:mm:ss) and I want to store this in a Navision datetime (let´s call this field "datetime") field.
I know that I can store the date (without time) using the "Evaluate" function, but this does not seem to work with datetime:
This does not work:
datetime := EVALUATE(datetime,COPYSTR(Value,9,2)+COPYSTR(Value,6,2)+COPYSTR(Value,1,4)+COPYSTR(Value,12,2)+COPYSTR(Value,15,2)+COPYSTR(Value,18,2))

This does work:
datetime := EVALUATE(datetime,COPYSTR(Value,9,2)+COPYSTR(Value,6,2)+COPYSTR(Value,1,4))

How can I store the complete datetime including the hours,minutes and seconds?
I think this is no problem for experienced Navision programmers, but as I said before this is quite new to me.
Maybe someone could give me a hint how to solve this problem?
Kind regards

garak

How do you get the MySQL DateTime Value? Do you use ADO do connect to a MySQL DB :?:

Andre1977

An external developer built an interface to exchange data between Navision and MySQL.
This allows me to fill Navision fields from MySQL using specific functions.
Unfortunately the Navision "datetime" has not the same format as MySQL "datetime" so I have to format it somehow.
In Navision I have this "variable" including the datetime in the MySQL Formate (yyyy-mm-dd hh:mm:ss) which needs to be formatted in order to be stored correctly in a Navistion datetime field.

garak

how do you get the MySQL DateTime Value?

Is it in NAV "on the fly" if you enter it in the MYSQL DB or did you import a file or is in NAV a button that you press and this connect to the MySQL DB and get from there your data?

Runs your Navisiondatabse under native or sql?

EDIT: If it is for example, a textfile that you import, check the CREATEDATETIME (Date,time) function that Luc posted ...

Luc_VanDyck

Have a look at the online-help for the CREATEDATETIME function:

CREATEDATETIME (Datetime)
Use this function to create a datetime from a date and a time.

DateTime := CREATEDATETIME(Date, Time)
DateTime
Data type: datetime
The concatenated datetime

Date
Data type: date
The date that you want to use to create a datetime.
You cannot use an undefined date to create a datetime.

Time
Data type: time
The time that you want to use to create a datetime.
You cannot use an undefined time to create a datetime.

Andre1977

Maybe my questions are too stupid or I have a problem explaining the problem correctly.

In Navision I have a string variable (name = "value") which is "2009-07-10 12:18:23" for example.
I want to store this in a navision database field (native db) which has datetime as datatype.

How can I format this string in order to make this work.

I just tested the following ways, but both did not work:
datetime := CREATEDATETIME(COPYSTR(Value,9,2)+COPYSTR(Value,6,2)+COPYSTR(Value,1,4),COPYSTR(Value,12,2)+COPYSTR(Value,15,2)+COPYSTR(Value,18,2))
datetime := CREATEDATETIME(EVALUATE(COPYSTR(Value,9,2)+COPYSTR(Value,6,2)+COPYSTR(Value,1,4)),COPYSTR(Value,12,2)+COPYSTR(Value,15,2)+COPYSTR(Value,18,2))

As I said I have poor skills in c/side programming and the online-help is not very helpful for me in this place.

Luc_VanDyck

Try this:

Value := '2009-07-10 12:18:23';
EVALUATE(intDay,COPYSTR(Value,9,2));
EVALUATE(intMonth,COPYSTR(Value,6,2));
EVALUATE(intYear,COPYSTR(Value,1,4));
ValueDate := DMY2DATE(intDay,intMonth,intYear);
EVALUATE(ValueTime,COPYSTR(Value,12,8));
datetime := CREATEDATETIME(ValueDate,ValueTime);

Variables used:

Value	    Text		30
intDay	   Integer		
intMonth	 Integer		
intYear	  Integer		
ValueDate	Date		
ValueTime	Time

The function CREATEDATETIME requires parameters of type Date & Time, not strings containing a date & time.

Andre1977

That´s it :-)
Thank you very much, Luc.
This time it worked, problem is solved.