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 @ 3:53 PM
Points: 38,063, Visits: 34,975
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, July 27, 2015 8:41 AM
Points: 1, Visits: 75
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, October 9, 2014 3:04 PM
Points: 5, Visits: 64
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
Posted Friday, June 5, 2015 12:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 1,731, Visits: 2,381
HI Jeff,
Is this possible to club the details of all the folders/files if one mentions any location/path???
This one is only working against files and a v nice one as it says what kind of filetype they are. But kind of consolidation would be great.


Thanks.
Post #1692165
Posted Monday, July 6, 2015 10:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 1,731, Visits: 2,381
Hi Lowell

To run your script, do I need to use vb.net alone or any other way available?


Thanks.
Post #1700537
Posted Monday, July 6, 2015 2:21 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 38,063, Visits: 34,975
SQL-DBA-01 (6/5/2015)
HI Jeff,
Is this possible to club the details of all the folders/files if one mentions any location/path???
This one is only working against files and a v nice one as it says what kind of filetype they are. But kind of consolidation would be great.


Sorry for the late reply.

Yes. You should be able to modify the code to do so.


--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 #1700611
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse