German Date Format to Access SQL compatible Date Format

Massimo Scola
Massimo Scola used Ask the Experts™
on
I want to insert the following data with SQL into an Access table, but I have problems with the German date format:

 If Not IsNull(txtDateTo) Then
    sql = "INSERT INTO tblKeyEmployees (kem_KeyID_FK, kem_EmployeeID_FK, kem_DateFrom, kem_DateTo) " _
       & "VALUES (" & Me.lstKeys & "," & Me.lstEmployees & ", #" & Format(txtDateFrom, "yyyy/mm/dd") & "#, #" & Format(txtDateTo, "yyyy/mm/dd") & "#)"
       
       Else

        sql = "INSERT INTO tblKeyEmployees (kem_KeyID_FK, kem_EmployeeID_FK, kem_DateFrom) " _
        & "VALUES (" & Me.lstKeys & "," & Me.lstEmployees & ", #" & Format(txtDateFrom, "yyyy/mm/dd") & "#)"
 End If
 
 
 
        Dim dbs As Database
        Set dbs = CurrentDb
            
        Debug.Print sql
        
        ' Select all records in the New Customers table
        ' and add them to the Customers table.
        dbs.Execute sql
             
        dbs.Close

Open in new window



This is the output of the SQL debug line

INSERT INTO tblKeyEmployees (kem_KeyID_FK, kem_EmployeeID_FK, kem_DateFrom) VALUES (1,3271, #2018.11.16#)

Open in new window


I have tried so many options now. What shall I do in order to properly format the German date version (dd.mm.yyyy) to a SQL compatible date?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Look at http://allenbrowne.com/ser-36.html Allen explains what is going on and what you need to do.  The easiest way is to use his simple SQLDate function and call it to properly format any date in queries and VBA code.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are very close. Your issue is, that the / is not a slash but the placeholder for your local date separator which is a dot.
Thus, Format inserts dots and not slashes, which SQL doesn't understand.

The solution is to escape the slashes by prefixing them with a backslash; then it will be read literally:

Format(txtDateFrom, "yyyy\/mm\/dd")

Open in new window

Distinguished Expert 2017

Commented:
SQL Server requires that all dates in queries be in US or universal date format.  mm/dd/yyyy OR yyyy/mm/dd.  How a date is displayed is controlled by your Windows settings or specific formatting you use on forms/reports/queries.  NEVER, EVER apply formatting at the table level.  This just obfuscates the actual value.  Neither the input nor the output date format affect how a date is stored internally.  Dates are stored internally (depending on the actual date data type) as a double precision number with the integer part being the number of days since some date in the past and the decimal being the elapsed time since midnight.  So noon would be .5  I don't recall the SQL Server origin date but Access uses 12/30/1899 as the "0" date so 2.5 = noon on 1/1/1900 and - 2.5 = noon on 12/28/1899.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial