Format problem: MySQL datetime -> Navision datetime

Andre1977
Andre1977 Member Posts: 5
edited 2009-07-10 in NAV/Navision Classic Client
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

Answers

  • garak
    garak Member Posts: 3,263
    How do you get the MySQL DateTime Value? Do you use ADO do connect to a MySQL DB :?:
    Do you make it right, it works too!
  • Andre1977
    Andre1977 Member Posts: 5
    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
    garak Member Posts: 3,263
    edited 2009-07-09
    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 ...
    Do you make it right, it works too!
  • Luc_VanDyck
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    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.
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • Andre1977
    Andre1977 Member Posts: 5
    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
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    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.
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • Andre1977
    Andre1977 Member Posts: 5
    That´s it :-)
    Thank you very much, Luc.
    This time it worked, problem is solved.

Categories