date format default for xp_cmdshell

[smatyk]

I have SQL statements that I run in Query Analyzer with character date values as parameters which work fine - QA recognises (for example) 20/12/2006 as dd/mm/yyyy. My QA is v 8.00.2039.

But when I run those same statements in xp_cmdshell, the implicit char-to-date conversion reads that as mm/dd/yyyy and of course fails.

I've tried both isql and osql in my syntax (see below) but always get the same (wrong) results. Obviously xp_cmdshell is reading a date setting default somewhere different than QA is. Where do I go to modify that default so all character date fields are recognised as dd/mm/yyyy?

exec master..xp_cmdshell 'isql -E dbname -Q "exec dbname..storedproc" -o output'
exec master..xp_cmdshell 'osql -E dbname -Q "exec dbname..storedproc" -o output'
 

Thanks, Sharon.

[iburyak]

Convert date to character before the output.
Use desired style of convert function. See example below:

[PHP]select convert(varchar, getdate(), 103)[/PHP]

[smatyk]

Hi - erm, I probably didn't explain well enough. I do know how to use convert and cast for different date formats - and anyway, char(10) format 103 is exactly my problem. That's dd/mm/yyyy but xp_cmdshell is reading that as mm/dd/yyyy and causing errors.

I have vb.net code that produces a page where the user can tick a box to select a report to run, and some reports have date parameters. So, the asp code picks up the characters typed for the date value - in the UK it means the user wants to type 20/12/2006 for example - and puts that into a variable. A stored procedure is run which places that variable into my data-select code; for example:

select * from table where trandate=@aspdate

When the stored procedure is run in QA I can feed in 20/12/2006 and the statement works fine. When the stored procedure is run via xp_cmdshell (using isql or osql syntax) then those same characters are being read as month then day and it errors.

I have a workaround in that if the user types 20-dec-2006 then I can use that value in my syntax as:

where trandate=cast(@aspdate as datetime)

and it all works again.

My issue is, there is some fundamental default US date format somewhere in our system. It ain't in QA, and the box on which SQL Server is installed appears to be fine with all UK regional settings. So, what is xp_cmdshell looking at, for it to do implicit conversions from aa/bb/cccc characters into an assumed mm/dd/yyyy date format?
Thanks, Sharon.

[navamnk]

use this statement in your SP

SET DATEFORMAT mdy

[iburyak]

You can't do select * this is a problem you have to list all columns in your select statement and convert date columns to text field with desired format this way it will never convert it back because it will be treated as text .

[PHP]
select column1, column2, column3, convert(varchar(30), trandate, 103) as trandate, -- continue list of other columns in a table here
from table where trandate=cast(@aspdate as datetime)[/PHP]