How to compare & extract specified length of files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to compare & extract specified length of files

Author

Topic

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2011-02-09 : 11:52:46

Dear All,

There are two backup files in a folder"E:\Test_Backup\"

TestDB_Backup_2011_02_08_230002_7317713.bak
TestDB_Backup_2011_02_05_230002_7318761.bak

Please let me know how to compare & get the latest file & also let me know how to extract the specified length of file name,I mean i want the result as "TestDB_Backup_2011_02_08" neglecting _230002_7317713

Please find the query below i have wrote,were its not satisfied my condition..

DECLARE @Date VARCHAR(10)
DECLARE @DD CHAR(2)
DECLARE @MM CHAR(2)
DECLARE @YY CHAR(4)
DECLARE @FileName VARCHAR(100)
DECLARE @DBName VARCHAR(50)
DECLARE @BkpPath VARCHAR(100)

SELECT @DBName = 'TestDB'

SELECT @Date = Convert(VARCHAR(10),Getdate(),121)
SELECT @YY = LEFT(@Date,4)
SELECT @MM = SUBSTRING(@Date,6,2)
SELECT @DD = RIGHT(@Date,2)

SELECT @FileName = @DBName + '_' + 'Backup' + '_' + @YY+ '_' + @MM + '_'+ @DD + '.BAK'

SELECT @BkpPath = 'E:\Test_Backup\'

SELECT @FileName = @BkpPath + @FileName

SELECT @FileName

Thanks & Regards
Ravi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-10 : 03:50:13

declare @t table(file_path varchar(100))
insert into @t
select 'E:\Test_Backup\TestDB_Backup_2011_02_08_230002_7317713.bak' union all
select 'E:\Test_Backup\TestDB_Backup_2011_02_05_230002_7318761.bak'
select top 1 file_path from @t
order by cast(replace(substring(right(file_path ,charindex('\',reverse(file_path))-1),15,10),'_','') as datetime) desc

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources