format question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

format question

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-07-06 : 13:33:14

This is an easy one, at least I hope it is...I've changed the '' around for like the last 1/2 hour with no luck. I'm sure one of you is looking at this and rolling their eyes but any help would be most appreciated.

I'm trying to insert a date into a file name without much luck.


Declare @Date	varchar(100);
Declare @Sql    varchar(MAX);

SELECT @Date= REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '');

print @Date

SET @Sql='exec sp_ReplaceFileOrDirNames @pathToObject= ''c:\farfiles'',
                              @oldName=''farUpload.txt'',
                              @newName=''FARADATAL_OFK_'''+ @Date + '.DAT'''
  
print @Sql

If you were to run this you would see an extra ' right before the date starts.


07062010
exec sp_ReplaceFileOrDirNames @pathToObject= 'c:\farfiles',
                              @oldName='farUpload.txt',
                              @newName='FARADATAL_OFK_'07062010.DAT'

How can I get rid of the extra '?

Thanks

Laura

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-07 : 00:36:16

Try this

Declare @Date varchar(100);
Declare @Sql varchar(MAX);

SELECT @Date= REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '');

print @Date

SET @Sql='exec sp_ReplaceFileOrDirNames @pathToObject= ''c:\farfiles'',
@oldName=''farUpload.txt'',
@newName=''FARADATAL_OFK_'+ @Date + '.DAT'''

print @Sql

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-07-07 : 08:19:55

Bloody brilliant! Thanks so much.

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources