DBQuery output format

[TheEek]

When I create a script to retrieve date data from a MySQL database, the dates are formatted to (I suppose) local settings. How can I get Macro Scheduler to return them as entered in the database.

example: A table with a value and date time.

Code: Select all

+-------+-------------------------+
| value  | timelog                     |
+-------+-------------------------+
| a        | 2013-01-18 14:42:30 |
+-------+-------------------------+

The Macro Scheduler code to retrieve the data:

Code: Select all

let>str=Driver={MySQL ODBC 5.1 Driver};Server=[server_ip];Database=[database];Uid=[user_id];Pwd=[password];
DBConnect>str,DB1
let>cmd=Select * from example where value ='a';
DBQuery>DB1,%cmd%,av,nr,mf;
mdl>%av_1_1%:%av_1_2%

The select statement on a MySQL console will return the above, but Macro Scheduler will return: "a:18/01/2013 14:42:30"

Which is wrong, I want "a:2013-01-18 14:42:30"
Hopefully there is a simple setting somewhere that doesn't require modification to the host OS. The only other way I can see this being done is by separating the output and putting it back together in a different order.

[Meryl]

Did you check the date/time commands in the help?

[TheEek]

I did, but nothing immediately springs out to me. Considering this is data retrieved from a database, and not using local time, can you give me a hint to which command I should look at please?

[Me_again]

Have you tried formatting the date by using the MySQL
DATE_FORMAT(date,format)
command in the Select? Maybe that will override the default.

[TheEek]

Thanks, I'll have a play with that. The only other way around this I can think of is to use a number midstr commands.

I really hate the way we are forced in to doing things with localisation. I can't think of any other reason why this would be so.

[Marcus Tettmar]

Just as a point of clarification: the format your date is being returned in is nothing to do with Macro Scheduler. Macro Scheduler performs ZERO analysis or parsing of information sent to or retrieved from the database. ALL it does is forward the query onto the database driver specified in the connection string and return the raw data that it returns. It is simply using Microsoft ADO and simply passing raw data back and forth.

So the date format must be down to the driver.

Now, when working with date types in SQL it is quite common to want to use the database date format function. This allows you to specify exactly what format you want returned (and what format you are sending). If you do not use it when inserting your dates could end up wrong in the database.

This is normal whether you're using VBScript,VB,C++ or Macro Scheduler.

[TheEek]

Thanks Marcus. I assumed this was a system (or similar) setting, but was hoping for further options in macro scheduler. I'd update the drivers, but I have little hope this will solve this small problem.

[Me_again]

So using date_format didn't fix it?