Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SQL Server 2000 for an old system web site. I've just hit an issue that is going to cause me endless hassles down the road.

When I installed the server on my local system I took all the defaults but now any datetime data type is getting stored in mm/dd/yyyy format.

Now this is not an issue for inserting data but when getting the data out and writing it to a page its in US format.

The site is going to be based in the UK on a UK server so I need my local development system to mirror that, and I also want to have to avoid using CONVERT on every field which is a datetime.

Tried changing the database collation a few times without success.

So my question was this:

Is it possible to change the format of the datetime so that inserting 23/01/1972 is stored as such and not 1/23/1972?

SERVER Properties:

  • Default language: English (United States)
  • Collation: SQL_Latin1_General_Cp1_CI_AS

Database Properties

  • Collation: SQL_Latin1_General_CP1_CI_AS

Specifically as as example

CREATE TABLE [dbo].[lbg_app_section1](
    [id_app_section1] [int] IDENTITY(1,1) NOT NULL,
    [refid_applications] [int] NOT NULL,
    [dateofbirth] [datetime] NULL,
    [gender] [nvarchar](50) NULL,
    [ninumber] [nvarchar](15) NULL,
    [address_hsno] [nvarchar](60) NULL,
    [address_twncty] [nvarchar](60) NULL,
    [address_cnty] [nvarchar](60) NULL,
    [address_postcode] [nvarchar](15) NULL,
    [address_country] [nvarchar](30) NULL,
    [dt_section1_modified] [datetime] NULL
) ON [PRIMARY]
share|improve this question
    
What is the datatype of the column? Char or date (or datetime)? –  ypercube May 4 '13 at 14:19
    
the data type is 'datime' –  John Cogan May 4 '13 at 14:24
4  
Datetimes (and dates) are not stored as strings. You really shouldn't be concerned about how they are stored. Only how you are sending (inserting) data to them and how you are retrieving them. What you notice is the default format of the output of datetime expressions. –  ypercube May 4 '13 at 14:30
1  
You can use CAST and CONVERT for output the datetimes in any format you like. –  ypercube May 4 '13 at 15:04
add comment

3 Answers

As stated in the comments, dates are not stored as "dates". They are actually stored as numbers. So there is no need to worry about that side of things. You can change the default output though by changing your language setting.

EXEC sp_configure 'default language', '23';
GO
RECONFIGURE
GO

This is the setting for British English. You can see all of them displayed in the syslanguages system view. syslanguages also contains the default date format. In the case of British English it is dmy. American English is mdy.

share|improve this answer
add comment

As stated by other posters, datetime values are stored as datetime values, not as strings. If you want to get technical, they are stored as two integers - one representing days and the other representing clock ticks (3.33ms for the datetime datatype).

I agree with you in not wanting to use CONVERT() on every field. I feel that the correct place for formatting region-dependant items is on the client. The web page or application should be formatting dates and times as appropriate to the user's locale. This can take advantage of any localisation features of the client programming language - something that T-SQL CONVERT() can not do.

As an aside, in case you didn't already know, the format for inserting dates can be controlled with SET DATEFORMAT.

As another aside, SQL Server 2008 added the date datatype which may make your life a lot easier.

share|improve this answer
add comment

A further aside that is relevant to your situation: I would recommend passing dates into SQL Server in ISO 8601 format (or a close approximation to it), as that's the least likely to be misinterpreted. SQL Server 2000 seems happy with accepting dates in yyyy-mm-dd hh:mm:ss format.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.