Convert date in TSQL

I am moving some data and I need to come up with a TSQL statement to convert dates currently in a datetime field to another database field with the varchar MM/yy format.

This statement needs to work on both SQL Server 2k5 and also SQL Compact Edition 3.5 - so the answer needs to be "set" based and not include cursors etc that are not supported in SQLCE.

From stackoverflow

• Check out the Books Online documentation for the DATEPART() Function.

• Not exactly what you want but you should be able to alter easily:

  DECLARE @ddate datetime
  
  set @ddate = getdate()
  
  SELECT CAST(DATEPART(month, @ddate) as varchar(2))  + '/' + CAST(DATEPART(year, @ddate) as varchar(4))
  

• http://msdn.microsoft.com/en-us/library/ms187928.aspx

  Convert to type 3 and trim the last three characters.

• How about something like this...

  select substring(convert(varchar(8),yourdatefield,3),4,5) as newdate 
  from yourtable
  

  So, for example,

  select substring(convert(varchar(8),getdate(),3),4,5)
  

  gives "02/09".

• relying on the type, i.e. "101" could be dangerous if you ever run on a non-US database server, as the mm and dd would be switched around. Probably the same with using type "3". As long as you know the language of the server will always be the same, those methods are the easiest.

  Using datepart is probably more reliable, but if you want say 03/08 instead of 3/08, you have to make sure to prefix the month with a "0", so

  select 
      right( '00' + convert(varchar(2), datepart( mm, @ddate)), 2) + '/' +
      right( convert(varchar(4), datepart( yy, @ddate) ), 2 )
  

  gleng : This is the best answer for our situation - as the server locale can vary. In hindsight I should have included that in the question. Thanks to all for your time and responses.

  Sung Meister : Yeah you are right. chopping 101 to 5th will give you MM/dd not MM/yy