DMS11X (3/11/2011)
I cant have the keys defined by MMDDYYYY-HHMMSS, Simply because we function 24hrs, and since we are on PST, every year in the fall the clock cycles back 1hr, essentially this would create the potential for duplicate keys as the clock has reset one hour every year... Trust me I was going to do this....
Did you check out the GetUTCDate() function? It's not affected by DST. 🙂
BTW, for sorting purposes, I'd suggest using YYYYMMDD-HHMMSS format. (Makes it a wee bit easier to find.) 😀
Now, to answer your question: use the undocumented system extended procedure xp_dirtree to load the file names into a table. Then count the number with that as the filename, and add one to it:
DECLARE @test-2 TABLE ([FileName] varchar(500), Depth tinyint, IsFile bit);
INSERT into @test-2
EXECUTE xp_dirtree 'D:\Temp\Videos\MCM Videos',0,1;
DECLARE @Counter smallint,
@Date char(8);
-- put the date in MMDDYYYY format
-- YYYYMMDD would be easier: convert(char(8), GetDate(), 112)
SET @Date = RIGHT('00' + CONVERT(VARCHAR(2), MONTH(GetDate())),2) +
RIGHT('00' + CONVERT(VARCHAR(2), DAY(GetDate())),2) +
CONVERT(CHAR(4), YEAR(GetDate()));
SELECT @Counter = COUNT(*) + 1
FROM @test-2
WHERE [FileName] LIKE 'AL-' + @Date + '%';
SELECT @Counter;
😎
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes