Converting a datetime to a numeric representation

Question

I want to convert a datetime field into a numeric representation in form of YYYYMMDD. So, my logic here is (from 2011-01-01 12:00:00.000 to 20110101) :

convert(int, replace(cast(getdate() as date), '-', ''))

According to MSDN ( http://msdn.microsoft.com/en-us/library/bb630352.aspx ), the string representation is [always?] "YYYY-MM-DD", so I simply convert that string to an INT after removing dashes from the string.

Will this always works? Will I encounter some problems with that? Is there a better way to achieve this?

Thanks

Answer 1

That approach can work, not sure what would happen with localization settings. If you use the built in datetime conversion function options (http://msdn.microsoft.com/en-us/library/ms187928.aspx) you can avoid using the replace and not worry about locales.

Example:

select CAST(convert(varchar,getdate(),112) as int)