how to get the date of a file using xp_cmdshell?

Question

So far I've this code

CREATE TABLE #tmp (strData VARCHAR(1000))
INSERT INTO #tmp EXEC xp_cmdshell 'dir C:\Users\Giba\Desktop\Folder\cmdshell1\*.txt'
SELECT * FROM #tmp --WHERE strData LIKE '09/25/2018'
DROP TABLE #tmp

but I want to select only date of the .txt files and compare them with current date. Can someone help to select only the date?

score 3 · Answer 1 · edited Apr 17 '25 at 18:04

This will depend on your regional settings, language, and potentially other settings.

...
WHERE TRY_CONVERT(date, LEFT(strData,10)) = CONVERT(date, GETDATE());

To avoid anything else from entering the #temp table in the first place, you can use forfiles, which allows you to specify a pattern (eliminating all the extra junk dir adds), specify a date (in this case any file with a modified date >= today), and also only return certain properties of the file (like the date), e.g.:

DECLARE @cmd varchar(4000) = 'forfiles /p C:\...\cmdshell1\ /m *.txt '
  + '/d +' + CONVERT(char(10), getdate(), 101) + ' '
  + '/c "cmd /c echo @fdate"';
INSERT #tmp(strData) EXEC master.sys.xp_cmdshell @cmd;
SELECT * FROM #tmp WHERE strData IS NOT NULL;

Results:

strData
----------
09/25/2018
09/25/2018
...

Your question says you only want the date value output, but that doesn't seem to make a lot of sense to me, since you already know the date, it will be the same on every single row, and all that really tells you is a count (so why not just run a count?). Here's how to pull out the file name with a similar technique (the for /f bit is to eliminate quotes around the @file output, as borrowed from this answer):

DECLARE @d char(10) = CONVERT(char(10), getdate(), 101),
  @cmd varchar(4000) = 'for /f %i in (''forfiles '
    + '/p C:\...\cmdshell1\ '
    + '/m *.txt '
    + '/d +' + @d + ' ' 
    + '/c "cmd /c echo @file"'') do @echo %~i';
INSERT #tmp(strData) EXEC master.sys.xp_cmdshell @cmd;
SELECT strData = @d + ' ' + strData 
FROM #tmp WHERE strData IS NOT NULL;

Results:

strData
--------------------------------
09/25/2018 file1.txt
09/25/2018 file2.txt
...

But I have to agree with Kin, this is much better suited for PowerShell no matter what you're actually looking for and whether it makes sense. xp_cmdshell was turned off by default starting with SQL Server 2005, and that was done for a reason. Let SQL Server focus on the data, and PowerShell and other things deal with the OS layer.

score 2 · Accepted Answer · answered Sep 25 '18 at 16:25

You have to just do some string manipulation. Below code will help you (adjust it as per your needs) :

CREATE TABLE #tmp (strData VARCHAR(1000))
INSERT INTO #tmp EXEC xp_cmdshell 'dir D:\dba\*.txt'

-- delete stuff that you dont need
delete from #tmp 
where strData is null
or strData like '% Volume %'
or strData like '% Directory %'
or strData like '%bytes%'
or strData like '%<DIR>%'

-- original 
SELECT * FROM #tmp --WHERE strData LIKE '09/25/2018'

-- just get the file date
select REVERSE( LEFT(REVERSE(strData),CHARINDEX(' ',REVERSE(strData))-1 ) ) as FileName,
          substring(strData, 2, PATINDEX('%[AP]M%', strData)) as FileDateTime
        from #tmp 
  -- borrowed from Aaron's below answer 
  --WHERE TRY_CONVERT(date, LEFT(strData,8), 3) = CONVERT(date, GETDATE());
  -- in America
  WHERE TRY_CONVERT(date, LEFT(strData,8), 1) = CONVERT(date, GETDATE());


-- clean up      
DROP TABLE #tmp

note: TSQL is not the best way to do file manipulation or comparison. You should use either PowerShell or SSIS to do the work.

how to get the date of a file using xp_cmdshell?

2 Answers2