Get File Date

This procedure gets the date a file was updated, as reported by the file system. 
exec sp_configure 'show advanced options', 1;  

reconfigure;  
exec sp_configure 'xp_cmdshell',1;  
reconfigure;  
go  
  
use master  
go  
  
create procedure [dbo].[get_file_date](  
@file_name varchar(max)  
,@file_date datetime output  
) AS   
BEGIN   
set dateformat dmy  
declare @dir table(id int identity primary key, dl varchar(2555))  
declare @cmd_name varchar(8000),@fdate datetime,@fsize bigint, @fn varchar(255)  
set @fn=right(@file_name,charindex('\',reverse(@file_name))-1)  
set @cmd_name='dir /-C "'+@file_name+'"'  
  
insert @dir  
exec master..xp_cmdshell @cmd_name  
  
select @file_date=convert(datetime,ltrim(left(dl,charindex('   ',dl))),103)   
from @dir where dl like '%'+@fn+'%'  
  
end  
go  


Usage:


declare @file_date_op datetime   
exec master.dbo.get_file_date   
  @file_name = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MSDBData.mdf'  
 ,@file_date = @file_date_op OUTPUT  
  
SELECT @file_date_op