xp_cmdshell format returned


I have the following problem ,

on some servers xp_cmdshell returns the date in this format

Volume in drive D is New Volume  
Volume Serial Number is A834-F9EE  
Directory of d:\sqlinstalls\cu6  
03/23/2011  01:51 PM    <DIR>          .  
03/23/2011  01:51 PM    <DIR>          ..  
02/04/2011  01:56 AM         8,119,808 sqlncli.msi  
02/04/2011  02:38 AM       292,314,464 SQLServer2008R2-KB2489376-x64.exe  
               2 File(s)    300,434,272 bytes  
               2 Dir(s)   6,529,056,768 bytes free  

and on others like this

Volume in drive C is OS
Volume Serial Number is 46FC-9B0C
Directory of c:\
20/04/2011  16:23           485,098 cybdefauth_i.log
25/04/2011  02:35            21,259 CybDefInstallInfo.log
20/04/2011  16:22               121 CybDefWebInstaller.log
24/08/2010  16:43    <DIR>          d7f152059018fbb2d699e6832fab2157
06/01/2010  12:28    <DIR>          dell
21/06/2011  19:54    <DIR>          Windows
               4 File(s)      1,411,182 bytes
              17 Dir(s)  77,513,871,360 bytes free

the problem is with the AM/PM,

I assume it is a locale setting,
Does anyone know what I can check to see the sql server regional setting ?
is it the collation ?

this is messing up a proc I am working on


more ▼

asked Jun 24, 2011 at 02:00 AM in Default

Gergav33 gravatar image

61 4 4 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest
The short date format set on the machine can be tested - see http://support.microsoft.com/kb/241671 for some more info on the registry setting. You can see how to read registry values at http://www.sqlservercentral.com/scripts/T-SQL+Aids/31071/
more ▼

answered Jun 24, 2011 at 06:55 AM

Dave_Green gravatar image

Dave_Green ♦
4.2k 3 4 8

(comments are locked)
10|1200 characters needed characters left

This will be set at the Windows Server level, not the SQL Server level.

Go to Control Panel of each server and check there
more ▼

answered Jun 24, 2011 at 02:04 AM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

thanks Kev I understand that ,

I need to be able to tell what it is so I can build a check into the t-sql when the proc is running

i need the date so I can perform date/time functions on it
Jun 24, 2011 at 02:21 AM Gergav33
Then you would have to build sql to check every possible format of date and time. This sounds more like you should be doing this kind of thing in SSIS - I'm sure there you could query the properties of a file to determine the creation date/time - a lot easier
Jun 24, 2011 at 02:25 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 24, 2011 at 02:00 AM

Seen: 1564 times

Last Updated: Jun 24, 2011 at 02:03 AM