[AccessD] Migrating date fields to SQL Server

[Gustav Brock]

Hi Charlotte

You must be thinking of the numeric zero value which for VBA equals 1899-12-30 while it for T-SQL is 1900-01-01.

However, you should only in rare cases deal with the numeric value of a date/time. Date and time should always be handled like dates, not strings, not numbers, no exceptions really.

The ODBC drivers know all about this. The current issue you may experience is, that the old ODBC drivers don't convert DateTime2 of T-SQL to DateTime but to Text. So, either use DateTime in your SQL tables or use the newer SQL Native drivers of version 11 or later. And never use Date, Time, or SmallDateTime as data type in your SQL Server fields.

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af Charlotte Foust
Sendt: 19. juni 2017 06:18
Til: Access Developers discussion and problem <accessd at databaseadvisors.com>
Emne: [AccessD] Migrating date fields to SQL Server

Nearly a decade ago when I was working in .Net and SQL, I knew how to migrate Access tables into SQL Server and handle the date field issues that arose.  I've forgotten it all, and I need to find a resource to refresh my memory and  help me do what needs doing.  I know it has to do with the differences between the dates in Access and SQL Server but I can't remember why it should be a problem moving the tables from Access with a shorter date/time to SQL with a longer one.  And I haven't a clue as to the mechanics of getting around the issue, aside from writing scripts, which I really would like to avoid.

Can anyone shed some light on this for me?


Charlotte Foust
(916) 206-4336