hello I have to output a date into a textfile in the following format ddmmyyyy How can I do this? Thanks for your help! Sorry I realise this was a little vague... Usman's comment was all I was looking for thanks!
If you want to convert the datetime to this format then that is pretty simple and this is where I am answering it as a comment. You could have googled to find so many ways like SELECT REPLACE(CONVERT(VARCHAR, GETDATE(), 105),'-', '') SELECT REPLACE(CONVERT(VARCHAR, GETDATE(), 104),'.', '') If something more required, then let us know.
You seem to have a penchant for answering questions never asked. Read the question posted and no where did the requester mention xp_cmdshell as a non-option. You may have a point if he responded with that concern. At this point I think your comment is redundant as to the user's request.
JHowe, your question is a little vague in the sense that it's unclear what precisely you want to accomplish. The below example presumes you are a member of sysadmin role or have xp_sqlagent_proxy_account on your server. I'm also presuming all you want done is select some date field from a table and output in the format e.g. 14122011. This example basically selects today's date, converts to 105 format and strips the hyphens, generates a text file called "
text.txt" at the root of C: The below should work for you as is, if you substitute SERVERNAME\INSTANCE with your own Servername and / or Instance. If you need more than has been provided, please be more specific. --Enable xp_cmdshell EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE Declare @cmdline varchar(4000) Select @cmdline = 'bcp "Select Replace(Convert(varchar(20),GETDATE(),105),''-'','''')" queryout "C:\
text.txt" -c -T -S SERVERNAME\INSTANCE' EXEC master..xp_cmdshell @cmdline