how to get the date of a file using xp_cmdshell?

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?

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.

StackExchange Database Administrators Q#218461, answer score: 3