SQL set vs select giving different results when using date/char

I am having trouble converting/editing dates in MySQL, probably be easiest if I show my issue:

INPUT:

declare @from_dattim2 datetime

set @from_dattim2 = (select dateadd(day,-30,'2011-07-18'))

print @from_dattim2

OUTPUT:

Jun 18 2011 12:00AM

which makes sense, then...

INPUT:

declare @from_dattim2 datetime

set @from_dattim2 = (select dateadd(day,-30,'2011-07-18'))

print @from_dattim2

select convert (datetime, @from_dattim2, 121)

OUTPUT:

2011-06-18 00:00:00.000

This is the output I want however it doesn't seem to work when I try to set my variable:

INPUT:

declare @from_dattim2 datetime

set @from_dattim2 = (select dateadd(day,-30,'2011-07-18'))

set @from_dattim2 = convert (datetime, @from_dattim2, 121)

OUTPUT:

J开发者_运维问答un 18 2011 12:00AM

Any ideas how I can set my variable to the format yyyy-mm-dd etc rather than Jun 18 etc?

Convert doesn't cause the datetime to be stored in a different manner. It only affects the formatting when output.

So you simply need to store it as a datetime and, when you select the data, thats when you do the convert() to the desired formatting.