Insert German date format in SQL using VBA

Question

I am very upset here, tried this for a few days ... All I want is for German users to enter dates that should be stored in SQL Server. Dates are in the following format: "dd.mm.yyyy". I always get error messages like this: Syntax date error in query expression '# 23.01.2004 00: 07: 00 #'. - using Access at the moment.

I tried many methods (Thread.CultureInfo, CDate (), Formatting DateTimeStyles), but here is my last one:

Dim dTermin As DateTime
dTermin = DateTime.Parse(txtTermin.Text)

sSQL = "INSERT INTO Termin (Termin) VALUES ("
sSQL = sSQL & "#" & dTermin & "#)"

It also gives me an error. However, if I encode a date like "10-04-2004", then it works. This is Access 2000 db, and there are two fields: ID (Autonumber) and Termin (ShortDate). Using the Jet OLEDB 4.0 Provider

Answer 1

You do not have to store localized dates in the database. Save them in a standard format, then let the ODBC connector or SQL client or whatever you use, localize it according to the culture of the person who is viewing them.

Answer 2

I suggest inserting non-localized dates in the database and localizing them only during display.

Answer 3

, datetime SQL Server 'eg '23.03.2009'

, SQL Server us_enlish.

SET LANGUAGE us_english
GO
DECLARE @dt datetime
SET @dt = '23.03.2009' --error
GO
SET LANGUAGE german
GO
DECLARE @dt datetime
SET @dt = '23.03.2009' --ok
GO

yyyydddd

SET LANGUAGE us_english
SET DATEFORMAT DMY
GO
DECLARE @dt datetime
SET @dt = '23.03.2009' --ok
GO

Answer 4

I would use string dates in the ISO format: YYYY / MM / DD Almost every system (and in every region) clearly understands the ISO format dates.

See Tibor Karaszi's excellent link: The Ultimate Guide to datetime datatypes