Date format

I'm trying to build a file-name variable that begins with a 'date'.

For example, I need to build logic in a T-SQL script that will access a file called 08032006abc123, with '08032006' being a representation of the date 8/3/2006.

Is there a quick/easy way to create this string, based on the date 8/3/2006? (would be nice if there was a date function that would zero-fill)

If you can use the date format YYYYMMDD or YYMMDD you can use CONVERT to get the encoded date ( http://msdn2.microsoft.com/en-us/library/ms187928.aspx ) else you can always do this and then rearrange the out string using the SUBSTR|||

The following syntax will return the date in the manner you are looking for:

DECLARE @.date datetime

SET @.date = GETDATE()

select replace(convert(varchar(10), @.date, 101), '/', '')

Results: 09082006