title: How to load files according to Created Date in Windows command shell via SQL Server's xp_cmdshell
body: <p>In SQL Server, I am using a query below to load all ".jpg" file names from a specific directory (e.g. z:) into a table. </p>
  
  <p>I want to know if there's a way to load files according to <strong>Created Date</strong> instead of <strong>Modified Date</strong> in Windows command prompt. The query below only works with <strong>Modified Date</strong> when executing <code>xp_cmdshell</code>.</p>
  
  <pre><code>-- Create the table to store file list
  CREATE TABLE myFilesTable (myFileID INT IDENTITY, myFileName NVARCHAR(256))
  
  -- Insert file list from directory to SQL Server
  DECLARE @Command varchar(1024) = 'z: &amp; forfiles /m *.jpg /s /d 07/16/2015 /c "cmd /c echo @fdate @ftime @path"'
  
  INSERT INTO myFilesTable
     EXEC MASTER.dbo.xp_cmdshell @Command
  
  -- Check the list
  SELECT * FROM myFilesTable
  GO
  </code></pre>
  
  <p><code>07/16/2015</code> in the variable <code>@Command</code> is the <strong>Modified Date</strong>. Obviously the command <code>forfiles</code> doesn't have a clue to filter files by <strong>Created Date</strong>.</p>
  
  <p>Below is a few results from the query given above in which FileNames are prefixed by <strong>Modified Date</strong>.</p>
  
  <pre><code>myFileID | myFileName
  ----------------------
  1        | NULL
  2        | 8/18/2015 11:13:08 AM "Z:\LDB1 App Export\Top Star_Aluminium Frames &amp; Furniture (B)-31267.jpg"
  3        | 8/19/2015 5:44:41 PM "Z:\LDB2 App Export\Soe Tint_Hardware Merchants &amp; Ironmongers-31435.jpg"
  4        | 8/19/2015 10:37:13 AM "Z:\Cover App Export\Taw Win Tun_Electrical Goods Sales &amp; Repairing (A) -31382.jpg"
  5        | 8/24/2015 10:34:33 AM "Z:\CP1 App Export\Thiri May_Fabric Shop (B)-30646.jpg"
  6        | 8/17/2015 10:08:39 AM "Z:\CP2 App Export\Ko Tin Aung_Building Materials (B)-31300.jpg"
  </code></pre>
  
  <p>I have also tried using <code>dir</code> command with timefield <code>/t:c</code> (the creation time) something like </p>
  
  <pre><code>EXEC MASTER.dbo.xp_cmdshell 'dir z: *.jpg /t:c /s'
  </code></pre>
  
  <p>It gives me the <strong>Created Date</strong> but it shows me the following result which is not as expected. I want the file names with full path/directory names as shown in the previous result.</p>
  
  <pre><code>myFileID | myFileName
  ----------------------
  1        |  Volume in drive Z is Publication
  2        |  Volume Serial Number is 3EF0-5CE4
  3        | NULL
  4        |  Directory of Z:\
  5        | NULL
  6        | 07/28/2015  06:41 PM    &lt;DIR&gt;          .
  7        | 07/28/2015  07:06 PM    &lt;DIR&gt;          ..
  8        | 03/05/2015  11:42 AM    &lt;DIR&gt;          LDB1 App Export
  9        | 03/05/2015  05:31 PM    &lt;DIR&gt;          LDB2 App Export
  10       |         0 File(s)              0 bytes
  11       | NULL
  12       |  Directory of Z:\LDB1 App Export
  13       | NULL
  14       | 03/05/2015  11:42 AM    &lt;DIR&gt;          .
  15       | 07/28/2015  06:41 PM    &lt;DIR&gt;          ..
  16       | 07/28/2015  06:49 PM         2,981,526 Kyaw Phay_Dental Equipment (A)-30998.jpg
  17       | 08/31/2015  03:10 PM         3,126,629 Venus_Fashion Shops-31438.jpg
  18       | 07/28/2015  06:49 PM         3,544,247 Marvellous_Tourism Services-30986.jpg
  ...      | ...
  </code></pre>
  
  <p>The expected result should be something like below,</p>
  
  <pre><code>myFileID | CreatedDate           | myFileName
  ----------------------------------------------
  1        | 8/10/2015 11:24:16 AM | "Z:\LDB1 App Export\Top Star_Aluminium Frames &amp; Furniture (B)-31267.jpg"
  2        | 8/10/2015 11:24:27 AM | "Z:\LDB2 App Export\Soe Tint_Hardware Merchants &amp; Ironmongers-31435.jpg"
  3        | 8/12/2015 10:05:22 AM | "Z:\Cover App Export\Taw Win Tun_Electrical Goods Sales &amp; Repairing (A) -31382.jpg"
  4        | 8/12/2015 10:05:22 AM | "Z:\CP1 App Export\Thiri May_Fabric Shop (B)-30646.jpg"
  5        | 8/12/2015 10:05:22 AM | "Z:\CP2 App Export\Ko Tin Aung_Building Materials (B)-31300.jpg"
  </code></pre>
  
  <p>Any help would be very appreciated :)</p>