Inserting date from Access DB into SQL Server 2008R2

Question

It seemed to be an easy task but I fail and do not find a solution for my problem: I have a local table in Access 2010 with a date/time column and I want to update a column in a SQL Server table with a datatype date.

Sending the date/time values direct to the SQL Server table fails, same with converting the date/time column with this VBA function:

Function DateForSQL(dteDate) As String
DateForSQL = "'" & Format(CDate(dteDate), "yyyy-mm-dd") & "'"
End Function

which gives

DateForSQL(Date()) = '2016-01-14'

and should work, I assumed.

The update command is this:

UPDATE SQL_table 
INNER JOIN local_table ON SQL_table.ID = local_table.ID 
SET SQL_table.DateField = DateForSQL(local_table.DateField)

But it fails again in Access with a type conversion error.

Even when changing the SQL Server table column to datetime I get the same error.

Same with sending to SQL a string like '14/01/2016' or '01/14/2016'.

The only thing I could do - eventually - is to change the datetime to text in Access and try again, but this could not be the only solution.

Any help?

Thanks Michael

marc_s · Accepted Answer · 2016-01-14 07:48:26Z

1

First of all, I recommend to use the ISO-8601 format for your date - which is YYYYMMDD (no dashes, nothing) - this works for all regional & language settings in SQL Server.

Next, I'm not sure about Access' SQL syntax, but in SQL Server, your UPDATE statement would be to be something like this:

UPDATE sql
SET sql.DateField = DateForSQL(local_table.DateField)
FROM local_table local 
INNER JOIN SQL_table sql ON local.ID = sql.ID

First UPDATE, then SET, then FROM and INNER JOIN ...

answered Jan 14, 2016 at 7:48

marc_s

760k185 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mak Over a year ago

Thks. The Access Syntax is correct, but even changing to ISO-8601 DateForSQL = "'" & Format(CDate(dteDate), "yyyymmdd") & "'" it fails again. But you guided me the correct way: I found the solution on changing my UPDATE string to .....SET SQL_table.DateFiled= Format(local_table.DateField,"yyyymmdd") Thandk, the Problem is solved! Michael

Collectives™ on Stack Overflow

Inserting date from Access DB into SQL Server 2008R2

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related