Testing Format vs Convert

SELECT TOP (1000) d = DATEADD(DAY, CHECKSUM(NEWID())%1000, o.create_date)
  INTO dbo.dtTest
  FROM sys.all_objects AS o
  ORDER BY NEWID();
GO
CREATE CLUSTERED INDEX d ON dbo.dtTest(d);

-- prime the cache
SELECT d,
  convert(char(10),d,105)+' '+convert(char(5),d,108),
  format(d,'dd-mm-yyyy hh:mm')
FROM dbo.dtTest;
GO

-- select all 1,000 rows
GO
SELECT d FROM dbo.dtTest ORDER BY d;
GO 5
SELECT d = convert(char(10),d,105)+' '+convert(char(5),d,108) FROM dbo.dtTest ORDER BY d;
GO 5
SELECT d = format(d,'dd-mm-yyyy hh:mm') FROM dbo.dtTest ORDER BY d;
GO 5

-- select top 1
GO
SELECT TOP (1) d FROM dbo.dtTest ORDER BY d;
GO 5
SELECT TOP (1) convert(char(10),d,105)+' '+convert(char(5),d,108) FROM dbo.dtTest ORDER BY d;
GO 5
SELECT TOP (1) format(d,'dd-mm-yyyy hh:mm') FROM dbo.dtTest ORDER BY d;
GO 5

-- force scan but leave SSMS mostly out of it
GO
DECLARE @d DATE;
SELECT @d = d FROM dbo.dtTest ORDER BY d;
GO 5
DECLARE @d CHAR(16);
SELECT @d = convert(char(10),d,105)+' '+convert(char(5),d,108) FROM dbo.dtTest ORDER BY d;
GO 5
DECLARE @d CHAR(16);
SELECT @d = format(d,'dd-mm-yyyy hh:mm') FROM dbo.dtTest ORDER BY d;
GO 5

SELECT

   case when t.text like '%format(%' then 'format' when t.text like '%convert(%' then 'convert' else '' end as func,
  s.execution_count,
  s.total_elapsed_time,
  avg_elapsed_time = CONVERT(DECIMAL(12,2),s.total_elapsed_time / 5.0),
  s.total_worker_time,
  avg_worker_time = CONVERT(DECIMAL(12,2),s.total_worker_time / 5.0),
  s.total_clr_time
  , [t] = replace(CONVERT(varchar(255), t.[text]),char(13)+char(10),'')

FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.[sql_handle]) AS t
WHERE 1=1
  AND t.[text] LIKE N'%dbo.dtTest%'
  and s.execution_count > 1
  and (t.text like '%format(%'
  or  t.text like '%convert(%')
ORDER BY s.last_execution_time;
GO

drop table dbo.dtTest;