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.

Do you want Access or SQL Server to recognise a date? it's not clear — gbn, Sep 18 '09 at 15:25
Sorry, I want Access to recognize the date, the Access DB has linked tables to SQL Server — pinku, Sep 18 '09 at 15:28
Thanks everyone for the fast responses! It is a huge help and appreciated! — pinku, Sep 18 '09 at 15:38

score 6 · Answer 1 · answered Sep 18 '09 at 15:27

6

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

answered Sep 18 '09 at 15:27

drAlberT

22,059
5
34
40

score 4 · Accepted Answer · answered Oct 01 '09 at 15:21

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.

answered Oct 01 '09 at 15:21

rkarajan

73
1
3

score 2 · Answer 3 · answered Sep 18 '09 at 15:27

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

score 2 · Answer 4 · answered Sep 18 '09 at 15:29

2

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

answered Sep 18 '09 at 15:29

Mitch Wheat

295,962
43
465
541

yyyy/mm/dd is not ISO. See this where I showed SQL 2005 does not recognise ISO yyyymmdd too http://stackoverflow.com/questions/1138142/best-way-to-convert-and-validate-a-date-string/1138203#1138203 – gbn Sep 18 '09 at 15:33
Agree with gbn: ISO 8601 is, even according to the linked article, YYYY-MM-DD. – OregonGhost Sep 18 '09 at 15:39
SQL Server 2008 does recognise 'yyyymmdd' as well as 'yyyy/mm/dd' – Mitch Wheat Sep 18 '09 at 15:44
SQL Server 2008 also recognizes ISO 8601. It's a lot safer to use it, since it's, well, an ISO. There's just no point in using something else that some software happens to accept, when it also accepts the standard solution. It's true though that ISO 8601 also requires a time, which I omitted in my previous comment. – OregonGhost Sep 18 '09 at 15:47

Inserting German date format in SQL using VBA

4 Answers4