delete files in a directory older than a month

Posted on 2006-04-20
Last Modified: 2012-06-27
I am attempting to create a sql command that will go to a given directory and delete out all files older than a month.

How do i do this?
Question by:DJMoonLight
    LVL 27

    Accepted Solution

    You could do this much easier with a windows script than t-sql. For example:
    You could schedule it as a job and run it as a CMDEXEC.

    To use t-sql you would have to use a filesystem object and DMO. Lots of code...

    I found this at this link (I have not tried it - I use VBScript):

    Create procedure USP_DelOldFiles @path varchar(25),@duration int
    --Objective: To delete files older than certain period from a folder
    --Usage example:
    --Exec USP_DelOldFiles 'c:\test',30
    -- which deletes files older than todaydate-30
    --Created by :MAK
    --Created date: Jan 7,2003
    --OS: windows 2000
    declare @myquery varchar(1000)
    declare @query varchar(1000)
    declare @name varchar(100)
    set @myquery = 'exec master.dbo.xp_cmdshell ''dir '+ ltrim(rtrim(@path)) +
    '\*.* /a/od'''
    print @query

    create table #Filenames (id int identity(1,1) ,name varchar(100))

    insert #Filenames(name)
    exec (@Myquery)
    delete from #Filenames
    where substring(name,3,1) <> '/'
    or name is null
    or substring(name,25,1) ='<'

    select name, SUBSTRING(name,7,4) + SUBSTRING(name,4,2) + left(name,2)
    from #Filenames

    /* Make sure dates are in comparable formats */
    Declare mycursor cursor for
    select name from #Filenames
    where SUBSTRING(name,7,4) + SUBSTRING(name,4,2) + left(name,2) <=
    CONVERT(char(8),DATEADD(d,@duration,getdate()),112 )

    open mycursor

    fetch next from mycursor into @name
    while @@fetch_status = 0
    set @query = 'exec master.dbo.xp_cmdshell ''del '+ @path + '\'+
    print @query
    exec (@query)
    fetch next from mycursor into @name
    close mycursor
    deallocate mycursor

    drop table #Filenames
    LVL 3

    Expert Comment

    I have to agree... Using the windows scheduler and then a simple utility like delOld.exe would be much simpler.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now