Date format problems in DTS ActiveX script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Date format problems in DTS ActiveX script

Author

Topic

PaulDavies
Starting Member

4 Posts

Posted - 2009-12-04 : 09:08:48

Hi

My DTS is performing a transformation which essentially takes 2 dates (start and end) and a value and phases the value across 12 months according to the dates and various other field values. The months values are represented as 12 money value fields in the destination table. The source data is a SQL view. Both source and target date fields are datetime

We are in the UK so the prefered date format is dd/mm/yyyy

A simple example from part of a record..
Promotion Start Date: 01/06/2009 (1st June)
Promotion End Date: 31/07/2009 (31st July)
Volume: 20,000

The result of this should post 0 in Jan, Feb, Mar, Apr, May. 9,836.06 in Jun, 10,163.93 in Jul and 0 in Aug, Sep, Oct, Nov and Dec. This is because the promotion lasted 61 days, 30 of which were in June and 31 in July.

There are all sorts of other factors involved, but this is the basic maths of it. The phasing calculations themselves are perfectly sound and have been in use for years in the current system that performs them. They are currently in the process of being moved to SQL Server.

This phasing is performed in an ActiveX script as part of a transformation task in DTS. It uses various DateDiff and DateAdd functions to get the result.

The script works perfectly well for me and a couple of other people, but for certain other users the result is different.
The permissions are the same for all users (except me) and they are set against an AD group.

It was discovered that the users had different Default Language settings, for me and those that it worked for, the Default Language was British English, for the others, just 'English' A quick bit of Googling and I found that this apparently also affected date settings, so this seemed to be the answer. All users were chanegd to British English.

The server has been rebooted, but this has made no difference to the results!

When the source data is previewed in the Transformation properties by the affected users the format appears as yyyy-mm-dd format, which is the same for me. When viewed directly in the source table the format is dd/mm/yyyy, which again is as expected.

I have set the transformation to export the variables in use to a file. The results show that for those users this fails for the dates formats are
Start: 6/1/2009
End: 7/31/2009

and for those it does work for the results are
Start: 1/6/2009
End: 31/7/2009
as expected. I'm also amazed that the transformations produce a result at all without raising an error

Is this just SQL server date settings or are user settings also involved?

I have tried formatting the date like this...
datStart = cDate(FormatDateTime(DTSSource("DateStart"),1))
where 1 represents vbLongDate but on the affected users this produces a runtime error, presumably because the format of source date the is not valid.

I have also tried setting the source dates to a varchar of the format yyyymmdd and during the transformation converting them to dates as dd/mm/yyyy. They still get stored and written to the log file as mm/dd/yyyy

I don't know what else to try.

The main question is why would the date format be different for different users (all now set to British English), and secondly how can I either change that or work around it!

This is the first time I have done any data transformations based on dates so am unsure if I am tackling this in the correct way.

Regards
Paul

PaulDavies
Starting Member

4 Posts

Posted - 2009-12-07 : 14:15:44

I managed to overcome the problem

It seems to me that the users affected were switched from English to British English after they had SQL Client installed and this change does not get picked up.

As for my DTS script - the fault lay in a function I have used for years to determine the number of days in a given month/year. I switched the function to

fnDaysInMonth = Day(DateSerial(iYear, iMonth +1, 0))

and it then made no difference what format the dates were in. Having never had a problem of forced US style dates I had never encountered the problem with this function before.

I have arranged an installation of SQL 2000 SP4 on the client machines too to bring them up to date.
Next year we are migrating to SQL 2005 anyway (just 5 years behind!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources