This seems to get the job done...

SELECT	RIGHT('00000000'	+	CONVERT(VARCHAR,REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')),8)

However, when I try to create it and CONVERT it to a DECIMAL, it then loses the "0"

SELECT	CONVERT(DECIMAL(8,0),RIGHT('00000000'	+	CONVERT(VARCHAR,REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')),8))

Can anyone please help me out with this? Is it impossible to convert it to a decimal and retain the leading "0" on the month?

My vendor's spec states..."Business Date Numeric For 8 positions MMDDYYYY"

Thanks for your review and am hopeful for a reply.

A numerical value generally does not have leading zero's.  I believe that the vendor is looking for a numerical string which has leading zero's but can be converted to a number.  Confirm that with your vendor.  If you are sending a flat file, then it doesn't matter because everything is a text field.

The numeric data types in SQL Server won't allow for leading zeros. The reason that it works in your first example is because you've converted to a varchar.  I'd also suggest a discussion with your vendor to confirm that they are looking for a string representation of a date data type.  The engine has great support for the date/datetime data types and many international date formats when using convert, and it is much better to store the data correctly on the back end and modify the presentation layer than trying to force the design when there are much better options.

Also, if you're using SQL Server 2012 or higher, you can use the FORMAT function to do the leading zero string conversion a bit simpler:

DECLARE @d DATETIME = GETDATE()
SELECT FORMAT( @d, 'MMddyyyy') as Output

Thanks,
Sam Lester (MSFT)

If they're wanting to hold a date in numeric format then MMDDYYYY sounds a terrible option, would they ever try to sort on this column and if so, do they really want Jan records for each year to be ordered together?

I'd say it needs to be your responsibility to talk them into revising the spec... Date & Time data should NEVER be stored as anything other than the appropriate Date/Time data types.

No qualified professional would ever create that as a spec and no qualified professional would develop the code to implement it, just because it ended up as a spec.

If all you're trying to do is export data as a flat file so that it can be consumed by the vendor, that's something else entirely. In that case they aren't asking you to use the NUMERIC data type (flat files don't have data types), they are just telling you that all 8 characters need to be numbers (0-9).