dbo.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