Problem importing dates using an ODBC call to SQL server

[Cameron Wellard]

Hi Everyone,

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.

[Dimitriy V. Masterov]

You should make sure you have the latest ODBC driver and also update Stata. I would also try grabbing this data without Stata to see if Stata is misbehaving.

Adding the datestring option can be another route.

[Cameron Wellard]

Thanks Dimitriy. I have extracted the same data to an Access database via an ODBC link using the same driver and was unable to reproduce the problem. I am about to update to Stata v18, which may solve the problem.

Cameron.

[Dan Palmer]

I am encountering the exact same problem using Stata 18.0. Using the same basic command described above, I am attempting to pull various date columns from a local database. Stata always returns all column headers, but sometimes certain entire columns are null, while other times the same columns will be fully populated. There seems to be no rhyme or reason as to when columns will/won't be populated. If anyone has any suggestions, please share.