Date transform into a standard format

It seems like the last couple of months I’ve been doing nothing but data cleaning and transformations. As a result, I’ve been having to create all kinds of strange temporary functions to manipulate data to extract the required information. Particularly, date transformations.

Of course, different installations of SQL Server (not to mention different database systems) handle dates in different ways - so it’s been important to handle them consistently. This little bit of T-SQL has been very useful in that regard…

declare @yday varchar(10)
select @yday = convert(varchar(4),year(dateadd(day, -1, getdate()))) 
   + '-'
   + right('0' + convert(varchar(2),month(dateadd(day, -1, getdate()))), 2) 
   + '-'
   + right('0' + convert(varchar(2),day(dateadd(day, -1, getdate()))), 2)

The Right function, to get the last two characters with a 0 in front, solves the problem of single digit dates. e.g. the 1st of the month, which in this case would be returned as 01.

The whole thing returns a 10 character varchar in the format yyyy-mm-dd - but it also works nicely if returned into a datetime variable. As written it works on todays date via the getdate() function.