获得文件修改时间的存储过程.sql

获得文件或者文件夹修改/访问时间的存储过程

use master

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pro_GetFileFloderLastDate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pro_GetFileFloderLastDate]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE proc pro_GetFileFloderLastDate
@FilePath  varchar (4000),
@DateType varchar (100) = 'DateLastModified',		-- DateLastAccessed：最后访问指定文件或文件夹的日期和时间，DateLastModified：最后修改指定文件或文件夹的日期和时间
@IsFile int = 1,	-- 1是文件，0是目录
@FileDate datetime output
as
  declare   @obj   int,@file   int
  declare   @fileexists   varchar(10)
  exec   sp_oacreate   'Scripting.FileSystemObject',@obj   output

  if @IsFile = 1
	exec   sp_oamethod   @obj,'FileExists',@fileexists   output,@filepath
  else
	exec   sp_oamethod   @obj,'FolderExists',@fileexists   output,@filepath

  if   @fileexists='False'
  begin
	set   @FileDate= 0
	return
  end

  if @IsFile = 1
  	begin
		exec   sp_oamethod   @obj, 'GetFile', @file   output, @FilePath
		exec   sp_oagetproperty   @file, @DateType,@FileDate   output
	end
  else
	begin
		exec   sp_oamethod   @obj, 'GetFolder', @file   output, @FilePath
		exec   sp_oagetproperty   @file, @DateType,@FileDate   output
	end
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO