Access Date/Time to SQL Datetime2(7)
2 tables:
1 - MS Access (2015/O365):
Fields Loan (Text) and BatchDateTime (Date/Time)
2 - SQL Server 2016:
Fields Loan (varchar(30)) and BatchDateTime (Datetime2(7))
Non passthrough query in Access:
Value of tbl1.batchdatetime = 1/25/2017 9:19:42 AM
The above SQL runs to no success for a type mismatch. I tried adding format to the mix via: FORMAT(tbl1.BatchDateTime,
I am stuck with Datetime2 as a datatype for the SQL due to vendor constraints.
I have attempted to search for an answer in EE but i find many going the other way, few going Access into SQL.
Need some guidance from the esteemed members of EE.
1 - MS Access (2015/O365):
Fields Loan (Text) and BatchDateTime (Date/Time)
2 - SQL Server 2016:
Fields Loan (varchar(30)) and BatchDateTime (Datetime2(7))
Non passthrough query in Access:
docmd.runsql "INSERT INTO dbo_tbl2 (Loan, BatchDateTime) " & _
"SELECT tbl1.Loan, tbl1.BatchDateTime " & _
"FROM tbl1;" Value of tbl1.batchdatetime = 1/25/2017 9:19:42 AM
The above SQL runs to no success for a type mismatch. I tried adding format to the mix via: FORMAT(tbl1.BatchDateTime,
I am stuck with Datetime2 as a datatype for the SQL due to vendor constraints.
I have attempted to search for an answer in EE but i find many going the other way, few going Access into SQL.
Need some guidance from the esteemed members of EE.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tried SELECT CONVERT(datetime2(7), tbl1.BatchDateTime ,120) with no success. Unknown Function CONVERT in expression.
Did you leave out the "11"? The driver is "ODBC Driver 11 for SQL Server"
As it happens, we don't update the tables that I didn't create (mine are all DateTime because I am aware of the problems) but I don't have any trouble reading the DateTime(2) as a datetime field. Using the old driver, that data type came over as a string so the dates wouldn't compare or sort as dates since they were strings.
I presume you relinked all the tables.
As it happens, we don't update the tables that I didn't create (mine are all DateTime because I am aware of the problems) but I don't have any trouble reading the DateTime(2) as a datetime field. Using the old driver, that data type came over as a string so the dates wouldn't compare or sort as dates since they were strings.
I presume you relinked all the tables.
ASKER
I did include the "11" i just mistyped. C/p it reads DRIVER=ODBC Driver 11 for SQL Server; Tables were relinked.
I am not though trying to shove a Datetime(2) and read it as date/time, (that would be SQL to Access, and both are technically datetime fields of similar length.
I am trying to shove a date/time into a Datetime2(7) (Access to SQL) and they are different formats.
I am not though trying to shove a Datetime(2) and read it as date/time, (that would be SQL to Access, and both are technically datetime fields of similar length.
I am trying to shove a date/time into a Datetime2(7) (Access to SQL) and they are different formats.
ASKER
For the sake of arguement, i changed BatchDateTime on tbl2 (SQL) side to datetime and still no dice.
NEVER format a date except for display. Internally dates are stored as double precision numbers and when you format them, you turn them into strings and they no longer operate like a date.
When you link the SQL Server table, does the date look like mm/dd/yyyy or like mm-dd-yyyy? If the latter, Access isn't seeing it as a date. Try to sort the recordset and see if all the january entries for all years come before the february entries for all years.
When you link the SQL Server table, does the date look like mm/dd/yyyy or like mm-dd-yyyy? If the latter, Access isn't seeing it as a date. Try to sort the recordset and see if all the january entries for all years come before the february entries for all years.
ASKER
On the SQL side there is no data in the table (until i append this data)
works fine, and producers data in the (SQL tbl) tbl2.BatchDateTime field in the format 1/26/2017 2:04:46 PM (displayed in access)
the (Access table) tbl1.BatchDateTime field has data formatted as 1/25/2017 9:19:42 AM (displayed in access)
DoCmd.RunSQL "INSERT INTO tbl2 (BatchDateTime) " & _
"VALUES (now());"works fine, and producers data in the (SQL tbl) tbl2.BatchDateTime field in the format 1/26/2017 2:04:46 PM (displayed in access)
the (Access table) tbl1.BatchDateTime field has data formatted as 1/25/2017 9:19:42 AM (displayed in access)
ASKER
@Pat - Accepting solution of ODBC driver change.
At some point last night/this morning i modified the tbl1.BatchDateTime field to text which caused the whole kerfuffle.
At some point last night/this morning i modified the tbl1.BatchDateTime field to text which caused the whole kerfuffle.
ASKER
Downloaded and installed on test/dev machine. Changed DNS from "DRIVER=SQL Server" to "DRIVER="ODBC Driver for SQL Server", relinked with new driver. Attempted to run the above SQL with same failed result of a type conversion error. Just as a note though, i am not using Datetime(2) but instead Datetime2(7) that is datatype datetime2 and not datatype datetime to a specificity of 2.