Inserting German date format in SQL using VBA

Question

I'm pretty frustrated here, been trying this for days... All I want is for German users to be able to enter dates that should be stored in SQL Server. The dates are in the following format "dd.mm.yyyy". I always get error messages like this one: Syntax error in date in query expression '#23.01.2004 00:07:00#'. - using Access for now.

I've tried lots of methods (Thread.CultureInfo, CDate(), Formatting DateTimeStyles) but here is my latest:

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

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

That also gives me an error. If I hardcode a date like "10-04-2004" then it works, however. It's an Access 2000 db and there are two fields: ID (Autonumber) and Termin (ShortDate). Using Jet OLEDB 4.0 Provider.

Answer 1

If I understand correctly, SQL Server datetime delimiter is ' eg '23.03.2009'

Then you have a 2nd problem, if SQL Server has the default us_enlish locale.

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

Use yyyydddd or this

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

Answer 2

I suggest to insert non localized dates in the DB, and localize them only at display time.

Answer 3

I would use string dates in ISO format: YYYY/MM/DD Pretty much every system (and in every locale) unabiguously understands ISO format dates.

See Tibor Karaszi's excellent reference: The ultimate guide to the datetime datatypes

Answer 4

You should not be storing localized dates in the database. Save them in a standard format then let the ODBC connector or SQL client or whatever you are using - localize it according to the culture of whoever is viewing them.