Querying from MySQL is returning missing values to datetime type.

[thiagoglima]

Hi,

I'm trying to query a datetime data from MySQL but it's returning missing values (just to datetime column, the other columns/ types return values). I already tried to change data format, convert to date but doesn't work for me, any help?

PS: Querying the same table from SQLServer returns the column date_invoiced correctly, from the MySQL don't.

Query:

Result:

_____________________________________________________________________________________

[HB]

Just to be clear, you are asking for MySQL syntax on a SAS forum without posting any code?

That about right?

[thiagoglima]

Hi HB, thanks for your answering.

the problem is not the syntax of mysql itself, I believe it may be related to some configuration of the library that interfaces with mysql (SAS ACCESS) or something like that, I have not solved the problem yet. Sorry if there is something wrong with my post or where I posted.

[ChrisHemedinger]

As you posted in the VA community, is this a behavior you're seeing the Data Builder?  If so, this might be more to do with how this data is loaded into LASR/CAS.  

Or are you working in a different SAS product?  

[thiagoglima]

Hi Chris, thanks for your answering.

Yes, my problem it's occurring on Data Builder, but I tested it on SAS Enterprise Guide and the problem also occurs over there, I also believe that the problem is related to how this data is loaded into LASR/CAS, but I have not solved the problem yet.

[SASKiwi]

What type of database connection are you using. I'm guessing it might be ODBC. If so check you have the most recent ODBC driver and if not test the latest one.

[thiagoglima]

Hi,

SASKiwi, I'm using SAS/ACCESS Interface to MySQL. I'll try with ODBC later, thank you.

Madelyin_SAS, it's possible, I'll check it and return here to inform the result, thank you.

[Madelyn_SAS · Accepted solution]

Is it possible that you are encountering this issue?

http://support.sas.com/kb/61/145.html

It appears that there is a hot fix (see Hot Fix tab).

[thiagoglima]

Thank you Madelyn_SAS, the hot fix worked perfectly.

Best Regards