American Dates

[edwardturner]

Hello,

I have a DTS that convert dates from a text file into dates to be held in SQL Server.

The source file dates are like so:

05OCT06

Therefore in my DTS I do the following conversion:

arrMonth = mid(DTSSource("Col003"),3,3)

if arrMonth = "DEC" then

arrMonth = "12"

elseif arrMonth = "JAN" then

arrMonth = "01"

I then use this to build the date into a UK date format valid for SQL Server:

CDate(left(DTSSource("Col003"),2) + "/" + arrMonth +
"/20" + mid(DTSSource("Col003"),6,2))

This is fine as I have used msgbox to output this during the transformation and the dates and being built ok.

However, when stored in SQL Server the month and day and being switched but only where it would be a valid american date, e.g. 05OCT06 becomes May 10th and October 5th.

Does anyone know why this might be happening. Is a setup issue in SQL Server? I am using SQL 2000 SP4.

 

[TomKane]

Hi,

It could be a script thing.... I seem to remember having a problem a bit like that and found that it was some kind of limitation with vbscript.

Is it absolutely necessary for you to convert to (for example) 05/10/2006? If the field you are passing the data into is a date field then 05-OCT-2006 would be acceptable to the target field and there would be no doubt as to what vbscript and SQL consider the actual date value to be.

Hope this helps,
Tom

 

[mrdenny]

SQL doesn't store the date/time data in a specific format. You can put the data in, in any of the valid international formats. The way that it is displayed to the user is controlled via the collation setting on the database, the users default language, and the language setting of the servers operating system (as well as however the client software is written).

If you can't change the collation of the SQL Server of database you can force the data to be displayed in the UK format by using the CONVERT function in the SELECT statements.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com