0

I've spent a few hours with this and still can't get it to work. I'm trying to get the path to the most recent file that matches a pattern for a dynamic SQL query where I'm loading a CSV file into a temp table for a stored procedure. The original SQL that I built the dynamic query on works perfectly fine (yes, I know CONCAT isn't in this one, but it shouldn't make a difference):

DECLARE @FILE varchar(MAX)
SET @FILE = '%InventoryFeedResults%.csv';

DECLARE @FILES TABLE (ID int IDENTITY, [FileInfo] varchar(MAX))
            INSERT INTO @FILES EXECUTE xp_cmdshell 'dir "C:\Inventory Feeds"'
            SELECT [FileName]
            FROM (SELECT ID
                        ,CAST(LEFT([FileInfo],18) AS datetime) AS 'Created'
                        ,RIGHT([FileInfo],CHARINDEX(CHAR(32), REVERSE([FileInfo]),1)-1) AS 'FileName'
                        ,ROW_NUMBER() OVER(ORDER BY CONVERT(datetime,LEFT([FileInfo],18)) DESC) AS 'RowNum'
                    FROM @FILES
                    WHERE [FileInfo] LIKE @FILE) AS FileInfo
            WHERE FileInfo.RowNum = 1

But when I try to run it here I get the "Conversion failed when converting date and/or time from character string" error:

DECLARE @DIR varchar(MAX)
SET @DIR = 'C:\Inventory Feeds\';
DECLARE @FILE varchar(MAX)
SET @FILE = '%InventoryFeedResults%.csv';

DECLARE @FINDFEED nvarchar(MAX), @PARAM nvarchar(MAX), @PATH nvarchar(MAX)
    SELECT @FINDFEED = 'DECLARE @FILES TABLE (ID int IDENTITY, [FileInfo] varchar(MAX))
                INSERT INTO @FILES EXECUTE xp_cmdshell ''dir "'+ @DIR + '" /b''
                SELECT @ADD = CONCAT(''' + @DIR + ''',[FileName]) 
                            FROM (SELECT ID
                            ,CAST(LEFT([FileInfo],18) AS datetime) AS ''Created''
                            ,RIGHT([FileInfo],CHARINDEX(CHAR(32), REVERSE([FileInfo]),1)-1) AS ''FileName''
                            ,ROW_NUMBER() OVER(ORDER BY CONVERT(datetime,LEFT([FileInfo],18)) DESC) AS ''RowNum''
                        FROM @FILES
                        WHERE [FileInfo] LIKE ''' + @FILE + ''') AS FileInfo
                        WHERE FileInfo.RowNum = 1'
    SELECT @PARAM = '@ADD nvarchar(500) OUTPUT'
    EXECUTE sp_executesql @FINDFEED, @PARAM, @ADD = @PATH OUTPUT

    SELECT @PATH

The dates are nvarchars in this format, EX: '2019-02-15 10:29:00.000'

The filenames are formatted with a report name and a 3 digit number, EX: InventoryFeedResults924.csv

The full output for FileInfo is:

02/15/2019  10:29 AM           405,609 InventoryFeedResults924.csv

This needs to work on SQL Server 12.

I've tried switching between CAST and CONVERT and a few different style numbers in CONVERT, but I'm honestly not sure why it would work one place and not the other. I just need to know what magical incantation that will let me convert this string to a datetime in a dynamic query so I can pull the most recent file if it's possible. Or maybe I could add an option to xp_cmdshell to format the timestamp to make it work? I'm at a loss, help me please, Internet!

closed as off-topic by MDCCL, LowlyDBA, Michael Green, Marco, Paul White Feb 19 at 9:10

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – MDCCL, LowlyDBA, Michael Green, Marco, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

1

Your second example is using the /b switch which only returns file names, but not any date information. Removing the /b should fix the problem.

Not the answer you're looking for? Browse other questions tagged or ask your own question.