Trouble with Time (Text ODBC driver)

I have a text file with Date and Time fields containing the values 01/01/2020 and, for the time field, 13:00:22

In Microsoft Query the values show as 2020-01-01 00:00:00 and 1899-12-30 13:00:22

When returned from MS Query to appropriately formatted columns in a Query Table they show as 01/01/2020 and 00:00:00

However, if I create a DateTime field as Date+Time it shows as 01/01/2020 13:00:22

How to get the Time to show correctly in the Query Table?

Time + 2 as [Time]

This shows as 13:00:22 in the Query Table. Took me ages to figure this out. I spent ages playing around with the DateTimeFormat option in the Schema.ini to no avail. It seems that if MS Query returns anything prior to 01/01/1900 00:00:00 the time portion will always show as 00:00:00 in Excel

[Microsoft] [ODBC Text Driver] Too few parameters. Expected:1

I had a complex query that returned the above error intermittently. It turned out that one of the fields was DATE (upper case) as specified in the schema.ini file for the text file. The query referred to Date (proper case). When the query was changed to refer to DATE (upper case) throughout, the intermittent failures stopped.

ODBC Text File Driver with Unicode files

I use ODBC links to text files on a regular basis and often have to join files coming from different systems.  Recently I had to join a Unicode file with an ANSI encoded one.  Simple, you’d think, as the scant documentation on the text files ODBC driver does mention dealing with Unicode.  However, the Define Text Format dialog used to configure the ODBC text driver (results stored in schema.ini) has only two radio buttons: ANSI and OEM.  I couldn’t get either to work with the Unicode file and so had to convert it to ANSI format before I could use it.  A real pain.  Then a eureka moment.  Manually edit schema.ini for the Unicode file entry to include the line CharacterSet=UNICODE.  It works! 

After spending ages on this I was frustrated to find a document called "Initializing the Text Data Source Driver" in information relating to Microsoft Office Access 2003.  The discussion appears to be about the Microsoft Jet database engine but it confirms that CharacterSet can also contain UNICODE or the code page number of a specific character set (e.g. 1232)

The relevant document is at:

http://office.microsoft.com/en-gb/access-help/initializing-the-text-data-source-driver-HP001032166.aspx?CTT=5&origin=HP001032168