How to load files according to Created Date in Windows command shell via SQL Server's xp_cmdshell

Here is one way you can parse the output of the DIR command:

--Create the table to store file listCREATE TABLE myFilesTable (myFileID INT IDENTITY, myFileCreateDate datetime, myFileName NVARCHAR(256))--Create temporary table to store output of DIR commandCREATE TABLE #DirectoryOutput (LineID INT IDENTITY, LineData NVARCHAR(256))--Insert file list from directory to SQL ServerDECLARE @Command varchar(1024) = 'dir z: *.jpg /t:c /s'INSERT INTO #DirectoryOutput   EXEC MASTER.dbo.xp_cmdshell @Command--Check the listinsert into myFilesTableselect     convert(Datetime,(left(LineData, 20))) CreateDate,    FilePath2.FilePath + '\' + right(LineData,len(LineData)-39) Filenamefrom #DirectoryOutputcross apply    (    select Max(LineID) LineID    from #DirectoryOutput FilePaths    where LEFT(LineData,14)=' Directory of '        and FilePaths.LineID < #DirectoryOutput.LineID    ) FilePath1join    (    select LineID, RIGHT(LineData, LEN(LineData)-14) FilePath    from #DirectoryOutput FilePaths    where LEFT(LineData,14)=' Directory of '    ) FilePath2on FilePath1.LineID = FilePath2.LineIDwhere ISDATE(left(LineData, 20))=1order by 1select * from myFilesTableGO

I slightly changed your table to include a separate column for the creation date:

CREATE TABLE myFilesTable ( myFileID           int IDENTITY                          , myFileName         nvarchar(256)                          , myFileCreationDate datetime                          )

You can use following PowerShell script to get the directory information and write it to the SQL table:

Import-Module "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS" -DisableNameChecking$files = Get-ChildItem "z:\" -Filter "*.jpg" -recurse | Where-Object { $_.CreationTime -ge "07/16/2015" } | Select-Object FullName, CreationTimeforeach ($file in $files) {    $creationTime = $file.CreationTime -f "dd-MM-yyyy hh:mm:ss"    $file.FullName = $file.FullName -replace "'", "''"    Invoke-Sqlcmd -ServerInstance "YourInstance" `              -Database "YourDatabase" `              -Query ("INSERT INTO {0} (myFileName, myFileCreationDate) VALUES ('{1}', '{2}')" `                            -f "myFilesTable", $file.FullName, $creationTime)}

Replace the YourInstance value with your instance name and the YourDatabase value with the name of your database.

I strongly advise against using xp_cmdshell as it open a windows command shell with the same security context as the SQL server service account. This is a security risk and it is best is to disable the xp_cmdshell command.

You can best execute the PowerShell command on the SQL Server. Prerequisite is that the SQLPS module is available (for the Invoke-SqlCmd commandlet). This prerequisite is met when you have installed SQL Server.

EDIT:

So if you really want to do it via xp_cmdshell, you can save the PowerShell script on your SQL Server and execute it the following way (I modified the path to the SQLPS file in the PowerShell script, assuming you have SQL Server 2012):

EXEC xp_cmdshell 'powershell.exe -file "C:\FileList.ps1" -ExecutionPolicy Unrestricted'

Where C:\FileList.ps1 is your saved PowerShell script.

forfiles only offers the modified date filter (see docs). Instead you can execute PowerShell from within SQL (e.g. like this) and since PowerShell has lots of nice filters it'll be easy to do that based on Created Date (e.g. like this).