Technical Article

return a file Last Modified Date

,

for instance

 

select dbo.GetFileDateLastModified('c:\temp\test.txt');

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [GetFileDateLastModified] (     @FilePathAndName varchar(8000))
      RETURNS datetime
AS
BEGIN
      DECLARE @FS int, 
            @OLEResult int, 
            @file_exists int,
            @DateLastModified datetime

      exec master.dbo.xp_fileexist @FilePathAndName,@file_exists OUTPUT

      IF @file_exists=1
      BEGIN
                  EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
                  IF @OLEResult <> 0 RETURN NULL

                  EXEC @OLEResult = sp_OAMethod @FS, 'GetFile',  @FS out,@FilePathAndName
                  IF @OLEResult <> 0 RETURN NULL

                  EXEC @OLEResult  = sp_OAGetProperty @FS, 'DateLastModified', @DateLastModified OUT
                  IF @OLEResult <> 0 RETURN NULL
      END
      ELSE
            RETURN NULL

      RETURN @DateLastModified
END

Rate

4 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating