SSIS date format problem

[taariq]

Hi

I have a big problem with an SSIS package. First of all it’s running on a
windows server 2003, SQL server 2005 and Office 2003. The package is
responsible for exporting data from a database to an excel sheet. There is a
field date which has been converted in the format DD/MM/YYYY in the SQL. When
I test the query in SQL server I got the right date format and also when I
run the package via the SQL server business intelligent development studio.

However my problem is that when it is executed via xp_cmdshell in a stored
procedure (called by an application), in the same Excel sheet I got date for
some records in the format DD/MM/YYYY and other records MM/DD/YYYY.

Can such thing be resolved? Is there is bug somewhere
Please help its urgent

[Todd C]

Hello:
Please help me to understand exactly what is going on. When you run your
package from your workstation using Visual Studio BIDS, you get dates
formatted as DD/MM/YYYY as expected, but when you run it by invoking
xp_cmdshell in a stored procedure, you get a mix of DD/MM/YYYY and MM/DD/YYYY.

Questions: The application that calls this stored procedure is on another
machine, yes? Have you checked the Regional Settings of the machines involved
(your workstation, the server, and any third-party executing machines)?

I think Excel ( and the default North American ) date format is MM/DD/YYYY.
BUT, if you give it a date of, for example 30/11/2009, then it *assumes* you
mean the 30th day of the 11th month, simple because it CANNOT be the 11th day
of the 30th month for obvious reasons. When you get a mix, does it appear
that the mixed up dates are always the 13th day of the month and later? If
so, you may need to adjust your package so that it either always returns
dates in MM/DD format as expected by Excel, OR have it return the month NAME
instead of a number, which would remove all doubt ("Nov. 11, 2009" or "11
Nov. 2009" would be interpreted correctly by Excel as dates without issue.

Good Luck.
=====
Todd C

Post by taariq
Hi
I have a big problem with an SSIS package. First of all it’s running on a
windows server 2003, SQL server 2005 and Office 2003. The package is
responsible for exporting data from a database to an excel sheet. There is a
field date which has been converted in the format DD/MM/YYYY in the SQL. When
I test the query in SQL server I got the right date format and also when I
run the package via the SQL server business intelligent development studio.
However my problem is that when it is executed via xp_cmdshell in a stored
procedure (called by an application), in the same Excel sheet I got date for
some records in the format DD/MM/YYYY and other records MM/DD/YYYY.
Can such thing be resolved? Is there is bug somewhere
Please help its urgent
.