Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    india
    Posts
    67

    Unanswered: how to get latest file

    how to get the name of the latest file of a particular directory
    thru query

    i.e
    i have files in a directory

    1130am.txt
    11.45am.txt
    1200am.txt

    my query should return 1200am.txt

  2. #2
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Re: how to get latest file

    Originally posted by vrsrinivas
    how to get the name of the latest file of a particular directory
    thru query

    i.e
    i have files in a directory

    1130am.txt
    11.45am.txt
    1200am.txt

    my query should return 1200am.txt
    Assuming that all your file names are prefixed with a time stamp then you should be able to use the MAX() function to return the desired record.

    Code:
    create table #test (theFileName varchar(20))
    
    insert into #test (theFileName) values ('1130am.txt')
    insert into #test (theFileName) values ('11.45am.txt')
    insert into #test (theFileName) values ('1200am.txt')
    
    select max(theFileName) from #test
    Shadow to Light

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm thinking the hard part is getting the info from the directory...

    Code:
    	Delete From Ledger_Folder
    
    	Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'
    
    	Delete From Ledger_Folder_Parsed
    
    	Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
    	Select	  Convert(datetime,Substring(dir_output,1,8)
    		+ ' ' 
    		+ (Substring(dir_output,11,5) 
    		+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
    		, Convert(Int,LTrim(RTrim(Replace(Substring(dir_output,17,22),',','')))) As File_Size 
    		, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
    	  From 	  Ledger_Folder
    	 Where  Substring(dir_output,1,1) <> ' '
    	   And (Substring(dir_output,1,1) <> ' ' 
    	   And  Substring(dir_output,25,5) <> '<DIR>')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •