Problem importing dates from an ODBC call to an SQL server

[Cameron Wellard]

Hi
I'm having a problem importing dates from an ODBC call to an SQL server. The problem is interesting in that it has a random element, sometimes the dates will be imported, at other times all the values will be missing. Additionally, if I call the same variable multiple times in one query, as per the following code

odbc load, exec("select ID as ID, DateofDiagnosis Dt1, DateofDiagnosis Dt2, DateofDiagnosis Dt3, Cast(DateofDiagnosis as varchar(30)) strDate  from tbl_Diagnosis") dsn("MyDB") clear

some fields may be populated and some missing, as shown below.

                  
ID    Dt1    Dt2    Dt3    strDate    
                        
1    19sep2018    .    19sep2018    2018-09-19    
2    08aug2018    .    08aug2018    2018-08-08    
3    16jul2018    .    16jul2018    2018-07-16    
4    30may2018    .    30may2018    2018-05-30    
5    11oct2018    .    11oct2018    2018-10-11                            
6    27jun2018    .    27jun2018    2018-06-27    
7    12jan2019    .    12jan2019    2019-01-12    
8    27sep2018    .    27sep2018    2018-09-27    
9    04feb2019    .    04feb2019    2019-02-04    
10    11oct2018    .    11oct2018    2018-10-11

Some points to note. If the values are missing they seem to be missing for all records, and if they are present they are present for all records. The missing fields have the correct data type and format. There is a degree of randomness to the error - it won't always be the same variables that have missing data, often they will all be populated. The problem only seems to manifest with dates, and if the date is cast to a string the sting value is always returned correctly.

I am using Stata/MP 16.1 on Windows Server 2016 Datacenter. The database is on Microsoft SQL Server.
My IT people have no explanation and blame Stata, but of course the problem may lie elsewhere, I'm not particularly technically inclined and don't really understand the mechanics of exactly how Stata talks to the database

Any advice would be appreciated.

Thanks,
Cameron.