create table dbo.mytable
  (type_log char(2) not null,
  date_log date not null,
  num_log smallint not null,
  description varchar(100) not null);
go
alter table dbo.mytable
  add constraint PK_mytable primary key (type_log,date_log,num_log);
go

DECLARE @test TABLE ([FileName] varchar(500), Depth tinyint, IsFile bit);
INSERT into @test 
  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
 WHERE [FileName] LIKE 'AL-' + @Date + '%'; 

SELECT @Counter; 

select
    -- Converted to varchar only for display purposes.
    -- Should be stored in a datetime2(7) datatype.
    DT_UTC	= convert(varchar(27),SYSUTCDATETIME(),121),
    DT_LOCAL	= convert(varchar(27),SYSDATETIME(),121)

DT_UTC                      DT_LOCAL
--------------------------- ---------------------------
2011-03-12 04:24:46.9012247 2011-03-11 23:24:46.9012247