Date formats connection properties

[seekwall]

I am having a few problems with importing date fields depending on whether the data is from a text file or an excel file ( with it's own column settings ).

I have reasoned that the solution lies with the default date settings in SQl Server (2000).

Using 'set dateformat', I set the format to DMY from MDY from my client. Using someone elses client however, I soon noticed that the setting for that client was still MDY. Further investigation told me that the connection properties actually override the server settings (including those set by the 'usp_set_start_options' system SP)

What I need to know, is how I change these connection properties to default to DMY - unfortunately, there doesn't seem to be any obious way of doing this.

Can anyone help me ?

 

[rt63]

These will depend on what client u r using.

Generally, it makes coding much easier if you can transfer data in YYYY/MM/DD or YYYY-MM-DD format - then u dont have to worry about date format settings on the server or the client.


RT

 

[seekwall]

Appreciated,

Still, I have no control of the input data as it comes from an external source.

What do you need to know about my machine(client) that will make a difference ?

Cheers

 

[rt63]

If you could elaborate a bit the process of data transfer
- is the user entering data through a front end like ASP page / VB form /PB window etc.
- is it a file which you are importing using DTS
- is it a file which you are doing a bulk insert

and maybe some sample data which is creating the problem.


RT

 

[seekwall]

Hi,

Basically the problem lies when inserting date values (using a simple DTS).

The Date Format on the SQL Server database has been set at MDY. The date field on the text file and excel file is in the 'dd/mm/yyyy' format. The column in the excel table is actually 'set' as 'dd/mm/yyyy' within the format column option.

If you DTS the data from the text file into the table in question, you will get a dd/mm/yyyy format returned. However, if you DTS the data from the Excel file into the table in question, you will get a mm/dd/yyyy format returned. The data in the text file and Excel appear to be the same though.

I believe that because Excel 'knows' the format of its data, it will explicitly re-format the data to the SQL Server default setting.

Cheers

 

[rt63]

You can try

Define a column with text type in excel and define a formula for it so that it converts the date column data into a string in format yyyy/mm/dd and while importing use this column instead of the original. Maybe you will be able to circumvent the problem (because SQL will handle implicit conversions)

or

Define the format for the column itself as yyyy/mm/dd.



RT

 

[seekwall]

Thanks RT,

I realise that there are ways of getting round the problem from the outset - there are pleny of options, however my original question was how to change connection properties ? In particular - Date Formats.

This issue nearly caused me to load a heck of a lot of bad data (lucky I was eagle eyed enough to spot the error !).

I am looking for a solution that can easliy be controlled without worrying about 3rd party data.

Cheers for your help

 

[seekwall]

Good afternoon, I believe I've found a solution to my own problem:

As stated above, the connection properties will override any server settings. So each client will have it's own default options, regardless of what has been set on the server side

In order to overcome this problem, the following SQL needs to be run sequentially:

execute sp_defaultlanguage sa, british
go

In order to change the data formats, we first need to change the language settings on the server. This can be done by changing the default language on the actual login (sa). A default language can be set when creating new logins.


sp_configure 'default language', 23
reconfigure with override
go

We then need to utilise the 'sp_configure' system stored procedure to change the global configuration settings for the server (23 is the language code for British English). By executing 'reconfigure with override', the change will be immediate. Otherwise, the changes will take place after the service is restarted.

Can anyone spot any flaws in what I have written ?