Convert date in TSQL

Question

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.

Answer 1

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

Answer 2

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))

Answer 3

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

Convert to type 3 and trim the last three characters.

Answer 4

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".

Answer 5

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 )