date time problem us to dutch - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

date time problem us to dutch

Author

Topic

Incognito
Starting Member

49 Posts

Posted - 2002-04-15 : 08:28:35

Hello,

I think you all had this problem once.

I want the user to insert,update,delete in dutch date formats.
DD/MM/YYYY
I want to search him also in dutch formats. (begindate to enddate).
If I do this, I get the usual error date out of range etc.
I am programming in ASP en SQL SERVER 2000!

What is the solution for this?

Thnx,

Gokhan

Nazim
A custom title

1408 Posts

Posted - 2002-04-15 : 08:35:29

Check for Convert function in BOL .

--------------------------------------------------------------

jackstow
Posting Yak Master

160 Posts

Posted - 2002-04-15 : 09:41:51

Oh this is one of my hobby horses - SQL Server and the 'I'm American therefore Christmas Day is 12/25/2002' thing.
There are several things to remember;

1. getdate() used as a default value will always give the correct date whatever the regional settings
2. inserted dates should be in the format yyyymmdd, i.e. '20020219' -this will always work as well
3. use convert() to get the date in the format you want, i.e convert(nvarchar(20),getdate(),113) will give you '19 Feb 2002 10:20:00'
4. don't use 'NOW' or 'Formatdate' in VBScript as these are dependent on the regional settings on the server

There was a thread about this here - http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=12889

jongregg
Starting Member

31 Posts

Posted - 2002-04-15 : 11:10:23

quote:

Oh this is one of my hobby horses - SQL Server and the 'I'm American therefore Christmas Day is 12/25/2002' thing.

Totally agree with you jackstow. I've never understood why the Americans decided on a dateformat that has no hieararchical properies whatsoever.

Have you also had the problem that I had last year where I would import dates into a datetime field:

11/12/2001
12/12/2001
13/12/2001

and would read the first 2 dates as 12th November 2001, 12th December 2001 and then get to the final date, realise that the date was not a valid American date so would swap them around so would import it as 13th December 2001?

Aaarrrrrrgh! is the only thing I can say.

Jon

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-15 : 11:45:25

Well now, I might chime in with some defense of our U.S. date format! (jeez, 227 years later, and these Limeys still harbor a grudge against upstart colonials who kicked them out)

How about the following dates, stored as string data, in DD/MM/YYYY format:

11/12/2001
12/11/2001
13/10/2001

As strings they are sorted correctly, but as dates they are not. As MM/DD/YYYY format:

12/11/2001
11/12/2001
10/13/2001

Both the string format and a pure date value will sort properly. Yes, I did choose double digit months and days for this example so they would sort correctly. However, none of the UK formats will sort properly as strings, using double or single digits. So there!

And when Microsoft moves their headquarters to the UK, then you can demand that they default to DD/MM/YYYY format

While I may not sound sympathetic, I am in truth, I've had the same issues. The best way is to do as Jackstow suggests, use the YYYYMMDD format (which also sorts correctly, I might add)

I've been passing dates this way for a while and it's been flawless.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources