Script to create DateStamp folder

• Arpan Shinde

  SSC Enthusiast

  Points: 151

  More actions

  December 8, 2009 at 12:50 am

  #137480

  I am trying to create date stamp folder and later move files to that folder through following script.

  But when i execute script it create folder but as %folderdate% and not as datestamp.

  where as when i execute three of the commands through command prompt it works fine. So can anyone help me to sort out this and tell where iam going wrong.

  declare @setdate varchar (100)

  declare @CreateDir varchar (100)

  declare @Filemov varchar (100)

  select @setdate='set folderdate=%date:~0,2%-%date:~3,2%-%date:~6,4%'

  print @setdate

  select @CreateDir='mkdir G:\Testing\test1\%folderdate%'

  print @CreateDir

  select @Filemov='move /Y G:\Testing\*.csv G:\Testing\test1\%folderdate%'

  print @Filemov

  exec master..xp_cmdshell @setdate

  exec master..xp_cmdshell @CreateDir

  exec master..xp_cmdshell @Filemov

• seth delconte

  SSCertifiable

  Points: 6388

  More actions

  December 8, 2009 at 7:40 am

  #1089231

  Try setting the date variable in SQL Server, not Windows:

  select @setdate = cast(datename(year, getdate())as varchar(10))+'_'+cast(datepart(month, getdate())as varchar(10))

  select @CreateDir='mkdir G:\Testing\test1\'+@setdate

  That way, your @CreateDir var prints:

  mkdir G:\Testing\test1\2009_12

  Windows assumes that %folderdate% is a system var, and can't find it.

  _________________________________
  seth delconte
  http://sqlkeys.com