CONVERTing DateTime to well-formatted NVARCHAR in T-SQL

I had no idea T-SQL's CONVERT had special codes for converting dates. Well, it does.

From How to use ROW_NUMBER() to enumerate and partition records in SQL Server - CodeProject:

Here is our final SQL statement, which achieves the business logic we wanted to implement.

Hide   Copy Code

SELECT
       [PersonID]
      ,[FamilyID]
      ,[FirstName]
      ,[LastName]
      ,[DateOfBirth]
      ,ROW_NUMBER() over(PARTITION BY FamilyID,
                         CONVERT(NVARCHAR(25), DateOfBirth, 111)
                         ORDER BY DateOfBirth ASC) TwinCode
  FROM [People]
ORDER BY PersonID

IIn the ROW_NUMBER function above, I am doing several things. I’m grouping on FamilyID, and also grouping on a converted DateOfBirth. I convert the DateOfBirth to an nvarchar using the 111 conversion code, because that gets results like ‘2009/10/11′ and ‘2009/10/12′ which can easily be grouped by to achieve distinct dates. [emph mine -mfn]

The full list is here, at docs.microsoft.com. Here's a taste...

Without century (yy) (1)	With century (yyyy)	Standard	Input/Output (3)
-	0 or 100(1,2)	Default for datetime and smalldatetime	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	1 = mm/dd/yy 101 = mm/dd/yyyy
2	102	ANSI	2 = yy.mm.dd 102 = yyyy.mm.dd