SQL Server date problem

[aliweb]

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!!

 

[aliweb]

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

 

[dlrmartin]

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

Martin

 

[antic]

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.

 

[papaja]

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.

 

[antic]

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