convert datetime (mm/dd/YYYY) to decimal(YYYYmmDD)

I am trying to get the following result in Sql:

example: 23/05/2014 to 20142305

but get this:

select convert(decimal, convert(datetime, '5/23/2014')) 

result:41780

anyone know how you can get the following format?? (if possible ??)

regards and thanks

Answer

In many databases (including SQL Server), you can just do:

select 10000 * year(datetime) + 100 * month(datetime) + day(datetime)

Some databases don’t support these functions, so you might need extract(datepart from datetime) or a similar function.