Can sys.xp_cmdshell dir return just the date of a file?

Question

I'm using sys.xp_cmdshell dir <filename> in order to get the last modified date of a file. For each file, it returns 7 rows. Among those 7 is a date and file name field. The data looks like this:

11/23/2015 12:14 PM 0 New Text Document - Copy (10) - Copy.txt

The date and the file name is separated by 17 spaces. Is there a way to get just the date? I'm finding splitting to be a difficult task, so I think it'd be easier to just get the date.

Check out this similar question: http://stackoverflow.com/questions/32293517/how-to-load-files-according-to-created-date-in-windows-command-shell-via-sql-ser — Brian Pressler, Nov 24 '15 at 16:06

Julien Vavasseur · Answer 1 · 2015-11-24T16:03:04.253

2

You can use dir like this:

sys.xp_cmdshell for /f %a in ('dir /b <filename.ext>') do @echo %~ta

It will only output the date for <filename.ext>.

If you want filename and date, use: @echo %a %~ta

edited Nov 24 '15 at 16:03

answered Nov 24 '15 at 15:52

Julien Vavasseur

3,854
1
20
29

score 1 · Accepted Answer · answered Nov 24 '15 at 15:27

You can insert your result into a temp table where the date can be parsed.

Edit: Added defensive code.

if object_id('tempdb.dbo.#files') is not null
    drop table #files;
begin
    create table #files
    (
        files varchar(200)
    )

    insert into #files
    exec sys.xp_cmdshell 'dir <filename>'

    select *,
        convert(date, substring(files, 1, PATINDEX('% %', files)))
    from #files
end

Hope this helps!

Can sys.xp_cmdshell dir return just the date of a file?

2 Answers2