Solomon Rutzky - Thursday, May 24, 2018 2:59 PM

Steve Jones - SSC Editor - Thursday, May 24, 2018 9:58 AM

I'll also add that I've worked on lots of systems that trundled along at 8% CPU and few IOPS all day long. Those systems could be written with cursor processing of child/parent relationships and FORMAT() everywhere and do fine. Some of them likely do with EF code in the front end.

FORMAT() has a place. A limited place, but still, it could be good

Nice article, Steve. And yes, absolutely there is a place for such functionality. Not every operation is executed frequently or against large datasets. Not all projects have appropriate dev resources to handle such things in the app code, just like not all projects have appropriate DB developer resources to do proper data modeling and/or coding.

Besides, trying to ignore something doesn't make it go away, so providing information, along with appropriate warnings, such as you have done, is the best approach. The fact that something can be used incorrectly, or inappropriately, is not a valid reason for not having that thing exist. Should we take away Dynamic SQL, LEFT JOINs, and pretty much anything that has ever existed?

---

Regarding the statement of " SSMS seems to think this is a NVARCHAR(250) ", the FORMAT function actually passes back NVARCHAR(4000). You can see this by either doing a SELECT INTO or by using the SQL_VARIANT_PROPERTY function as follows:

SELECT
  FORMAT(GETDATE(), 'd'), -- 5/24/2018
  SQL_VARIANT_PROPERTY(FORMAT(GETDATE(), 'd'), 'BaseType'), -- nvarchar
  SQL_VARIANT_PROPERTY(FORMAT(GETDATE(), 'd'), 'MaxLength'); -- 8000

It most likely passes back the full 4000 because the format string can contain extra stuff and could technically be 4000 characters. For example:

SELECT
  FORMAT(GETDATE(), 'lalala $$$$$ d ----------- M ---------- yyyy >>>>>>>>> mm ... MMM');

-- lalala $$$$$ 24 ----------- 5 ---------- 2018 >>>>>>>>> 50 ... May

And while you did mention that the FORMAT built-in function was added in SQL Server 2012, I will mention that it is possible to get this functionality in SQL Server 2005, 2008, and 2008 R2 (for those still stuck on those versions) via SQLCLR. The SQL# SQLCLR library (that I wrote, and this function is available in the free version) comes with a "Date_Format" function that does the same thing that the built-in FORMAT function does, and it works on SQL Server 2005, 2008, and 2008 R2. The following statement (which requires SQL Server 2012 or newer as it compares both functions)

SELECT
  SQL#.Date_Format('2018-05-24', N'<yyyy> [MMM]', 'he'),
  FORMAT(CONVERT(DATE, '2018-05-24'), N'<yyyy> [MMM]', 'he');

returns the same value for both columns:

<2018> [מ××™]