Hi,
How can I get output like this format 2006-11-27 06:44:55 from this 11/27/2006 06:44:55 am.
I also need this format in datetime datatype without AM/PM.
Need you suggestions, thanks.
Are you saying that your input is a string, not a utcDateTime value? If so, you need two steps: first parse the string to datatime and only then create a formatted string from it.
Regarding the conversion to string, there are several options. As I mentioned in your previous thread, I like using System.String::Format() - it’s simple and easy to read:
utcDateTime value = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::utcNow(), DateTimeUtil::getUserPreferredTimeZone());
str s = System.String::Format('{0:yyyy-MM-dd HH:mm:ss}', value);
How can I change this string type output to datetime datatype without AM/PM.
You’re confusing datetime and string data types. Datetime doesn’t have any string format.
You can decide which format you want to use (e.g. if it should use 12-hour format or 24-hour format) when you’re converting a datetime to string. That’s exactly what my code above demonstrate.
Let me try to explain this.
I have date in this format 4/28/2019 01:20:07 pm (type datetime).
But I need to change in this format 2019-4-28 13:20:07 (required type datetime not string).
Because I have to save this date in datetime datatype table column.
Thanks in advance.
No, utcdatetime doesn’t have any string format. What you’re looking at is just one of many possible string representation of the datetime value, not the etcdatetime value itself. utcdatetime is internally implemented as a structure with a bunch of numeric fields, while you seem to think that it’s stored as a string in a particular format.
As there is no associated string format, the requirement to chnange it makes no sense. The moment where you can apply a particular string format is when you take a datetime value and you want to convert it to string.
If you tell me more about what you’re trying to achieve from business point of view, I should be able to help you build a solution that is technically feasible.
Actually I am trying to insert AX SQL Database data into MYSQL database.
Facing this error message (SQL error description: [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.40]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘1:48:00 PM)’ at line 1)
AX Column datatype : datetime (format: 3/17/2021 06:48:00 pm)
MySQL Column datatype : datetime (2021-3-17 06:48:00)
I don’t know your code, but your previous thread suggests that you’re generating SQL code. Because SQL is text, you need to convert your datetime value to text in the format expected by MySQL (you’re currently using a wrong format). You can find a concrete example in my first reply.
I am sharing with you my code:
Try 1:
sql_header = strFmt(“INSERT INTO OGP_Header (OGP_Id, OGP_Date) VALUES (’”+inventSiteGateEntry.SLDInternalOGPNumber+"’,’"+inventSiteGateEntry.ArrivalDateAndTime+"’)");
Error : Operand types are not compatible with the operator.
Try 2:
sql_header = System.String::Format(“INSERT INTO OGP_Header (OGP_Id, OGP_Date) VALUES (’”+inventSiteGateEntry.SLDInternalOGPNumber+"’,{0})", inventSiteGateEntry.ArrivalDateAndTime));
Error : SQL error description: [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.40]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘1:48:00 PM)’ at line 1
Try 3:
str s;
s = System.String::Format(’{0:yyyy-MM-dd HH:mm:ss}’, inventSiteGateEntry.ArrivalDateAndTime);
sql_header = System.String::Format(“INSERT INTO OGP_Header (OGP_Id, OGP_Date) VALUES (’”+inventSiteGateEntry.SLDInternalOGPNumber+"’,{0})", s));
Error : SQL error description: [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.40]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘13:48:00)’ at line 1
Note : inventSiteGateEntry.ArrivalDateAndTime is datetime datatype.
MySQL column (OGP_Date) also has datetime datatype. (format in which store data like : 2021-3-17 06:48:00)
You should do things one by one. Now you’re trying to generate code from AX data without knowing how the code should look like.
Forget AX for a moment and try to write SQL code for the insert. Only when you know how to do it, write X++ code generating this code. Refer to MySQL documentation (9.1.3 Date and Time Literals) if it’s not clear to you how to specify the value.
You’ll see that MySQL expects the value in quotation marks, if you use the format from your third piece of code. The second one is using invalid format. The first one is syntactically wrong.
By the way, please use Insert > Insert Code to paste source code.
Again thank you Martin, its working now.