CODEWITHSUNDEEP
sqlsql-server
goofyui
goofyui

Reputation: 3492

How to get file create date in SQL?

Using openrowset, I am loading the XML file to a temp table.

How do I get the file created date?

CREATE TABLE #T 
(
    IntCol int,
    XmlCol xml
);

INSERT INTO #T (XmlCol)
    SELECT *
    FROM OPENROWSET(BULK 'c:\Test.xml', SINGLE_BLOB) AS x;

SELECT * FROM #t

Upvotes: 1

Views: 11471

Answers (3)

Shaybakov
Shaybakov

Reputation: 756

declare @f varchar(1000)='E:\work\share\data.txt';
declare @cmd varchar(1000)='for %a in ('+@f+') do echo %~ta'
  IF OBJECT_ID('tempdb..#t1') IS NOT NULL
  DROP TABLE #t1
  create table #t1(_output varchar(1000))
  insert #t1
  exec xp_cmdshell @cmd
  declare @fileDate varchar(100)
  set @fileDate=(select _output from #t1 where _output not like '%echo%')
  print @fileDate

and convert @fileDate to dateTime by your settings

Upvotes: 0

TT.
TT.

Reputation: 16146

Not the most concise way to do this, but using Ole Automation in SQL Server is one way to get this information. The following sample uses C:\Temp\testfile.txt as an example. This not really "SQL", don't know if this good enough for you.

DECLARE @hr INT;
DECLARE @dt_created DATETIME;
DECLARE @obj_file INT;
DECLARE @obj_file_system INT;
DECLARE @file_name VARCHAR(100)='C:\Temp\testfile.txt';

-- Create a FileSystemObject. Create this once for all subsequent file manipulation. Don't forget to destroy this object once you're done with file manipulation (cf cleanup)
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @obj_file_system OUT;
IF @hr<>0 GOTO __cleanup;

-- Get a handle for the file. Don't forget to release the handle for each file you get a handle for (see cleanup). The return will be different from 0 if the file doesn't exist
EXEC @hr = sp_OAMethod @obj_file_system, 'GetFile', @obj_file out, @file_name;
IF @hr<>0 GOTO __print_created_date;

-- Retrieve the created date.
EXEC sp_OAGetProperty @obj_file, 'DateCreated', @dt_created OUT;

__print_created_date:
SELECT @dt_created AS file_date;

__cleanup:
EXEC sp_OADestroy @obj_file_system;
EXEC sp_OADestroy @obj_file;

Ole Automation needs to be enabled first though (just once):

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Upvotes: 3

M. Rezaeyan
M. Rezaeyan

Reputation: 408

T-SQL is not a language with which you can access file systems. However you can write a stored procedure in C# to accomplish this task. You would read the metadata with the appropriate classes in the .Net Framework. You can write a custom function with CLR integration to get every information you need from your filesystem.

Here is a little working sample to get an file created date with CLR integration in C#:

public class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlDateTime GetCreatedDate(SqlString filePath)
    {
        return filePath.IsNull ? SqlDateTime.Null : File.GetCreationTime(filePath.Value);
    }
}

Then you've got to deploy the assembly and register it to SQL Server, then create the proper function with Create function commands.

see more in CodeProject Sample

Upvotes: 1

Related Questions