SQL Server date problem

General Web Coding Microsoft Development
1

Hello

Let’s assume the date is 8 Dec 2006.

I have SQL Server 2000 installed on Windows Server 2003.
The regional settings are set to English (US) which has date format of 12/08/2006.

Problem is when records are inserted in SQL Server they are entered as 08/12/2006.

I changed regional settings to English (UK) which has date format of 08/12/2006.

Now when I enter records in SQL Server they are entered as 12/08/2006.

Why is that? When I choose regional settings as US it save as UK date and when I choose regional settings UK it saves as US date!! :frowning:

2

By the way I am using ASP.Net 2.0 to enter records and the function I am using is Now()

3

you should change the default language for the sql login. surely it is in spanish.

Martin

4

There is only one proper way to insert dates into SQL Server. Use a region-independent format. You should only worry about the region when displaying dates, not storing them.

  • For storing dates, the ISO standard is “yyyymmdd”.
  • For date and time, it’s “yyyymmdd hh:mm:ss” (24-hour time).
  • Use single quotes around the date string.
  • If possible, always store dates in UTC time (universal time, i.e. Grenwich mean time).

For example, to insert 8 Dec 2006:

INSERT INTO tablename (datefield) VALUES (‘20061208’)

To insert 8 Dec 2006, 5:30pm:

INSERT INTO tablename (datefield) VALUES (‘20061208 17:30:00’)

Hope that helps.

5

Actualy,
When inserting anything in sql, you should use parameters, for security reasons. Also, this way you will not have problems with dates or other data types.

6

True, that is ideal. Either way is ok depending on sql skills at the time.