Make sure month and day are two char long and build the date string : Date calculation « Date Functions « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Date Functions
3. Date calculation

4> DECLARE @MonthChar VarChar(2), @DayChar VarChar(2), @DateOut Char(8)
5> SET @MonthChar = CAST(MONTH(GETDATE()) AS VarChar(2))
6> SET @DayChar = CAST(DAY(GETDATE()) AS VarChar(2))
7>
8> -- Make sure month and day are two char long:
9> IF LEN(@MonthChar) = 1
10> SET @MonthChar = '0'
11> IF LEN(@DayChar) = 1
12> SET @DayChar = '0' + @DayChar
13> -- Build date string:
14> SET @DateOut = @MonthChar + @DayChar + CAST(YEAR(GETDATE()) AS Char(4))
15> SELECT @DateOut
16> GO

--------
0112008

(1 rows affected)