Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How to get file date using xp_cmdshell? Expand / Collapse
Author
Message
Posted Tuesday, July 17, 2012 6:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 36,959, Visits: 31,472
Thanks but if you can use CLR, the solution that Lowell posted is better.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1331156
Posted Tuesday, June 18, 2013 3:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 5, 2013 10:44 AM
Points: 1, Visits: 73
I don't know if its too late to answer or not . here is the way to get date .

CREATE TABLE #mytable ( Line VARCHAR(512))
SET @PathName = '\\uncpath\foldername\flodername\foldername\' --if you have unc path else use c:\ etc.
SET @CMD = 'DIR ' + @PathName + ' /TC'
INSERT INTO #mytable
EXEC MASTER..xp_cmdshell @CMD
DELETE FROM #mytable
WHERE Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR Line LIKE '%<DIR>%'
OR Line is null

SELECT TOP 1 FILENAME ,Createdate into ##Onlydate_formytab FROM
(SELECT TOP 1000 REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ) AS FILENAME ,
---1 need to change if you have space in source filename like 'my excel name 20130202.xlsx'
right(LEFT(Line,10),4)+LEFT(left(Line,10),2)+SUBSTRING(LEFT(line,10),4,2)

as Createdate FROM #mytable ORDER BY REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ) DESC)s

plz mark as answer if this answer your question
Post #1464895
Posted Wednesday, October 2, 2013 7:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:04 PM
Points: 3, Visits: 60
Thank you! Much more straightforward than other solutions seen, i.e., you just delete the lines that don't match the date mask. Nice!
Post #1500962
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse