title: SELECT Latest Full Backup File - T-SQL
body: <p>Trying to SELECT the latest full backup file from a temporary table using the below query:</p>
  
  <pre><code>CREATE TABLE #fileList (backupFile NVARCHAR(255))
  
  SET @backupPath = '\\ServerName\SQLBACKUP\Full\' 
  SET @cmd = 'DIR "' + @backupPath + '"'
  
  INSERT INTO #fileList
  EXEC master.sys.xp_cmdshell @cmd 
  
  SELECT @lastFullBackup = MAX(backupFile)  
      FROM #fileList  
          WHERE backupFile LIKE '%Production_Backup%'
  </code></pre>
  
  <p>However, it is selecting 12/31/2016 backup, instead of the latest. I believe the reason being is because when the data is loaded into the temp table it loads the date as MM/DD/YYYY instead of YYYY/MM/DD and SQL is only looking at the month and not the year. </p>
  
  <p>Here is how the data looks in the temp table:</p>
  
  <pre><code>12/31/2016  01:04 AM    24,418,292,736 Production_backup_2016_12_31_000000_4880248.bak
  </code></pre>
  
  <p>NOTE: There's only one column in the temp table, so I can't convert the date to YYYY/MM/DD format. </p>
  
  <p>How can I get the latest backup?</p>
  
  <p>FYI-This is the link I referenced: 
  <a href="http://www.sqlideas.com/2009/07/auto-generate-sql-server-restore-script.html" rel="nofollow noreferrer">http://www.sqlideas.com/2009/07/auto-generate-sql-server-restore-script.html</a></p>