SQLTeam.com | Weblogs | Forums

Error message with the Format Function

Hello everyone,

I want to use the format function to convert a datetime column into MMM-YY format. Here is the code I have at the moment:
SELECT su.updateDate, FORMAT(su.updatedate, 'MMM-yy') AS Audit_Month
FROM Sales.Update su

So if the date is 2005-07-01 I want the output to be Jul-05. At home and at work I am using the same version of SSMS (v17.8.1) The query works perfectly at home but at work I am getting the following error message
"FORMAT is not a recognised built in function name"

I don't understand the error as I believe the function should work with this version of SQL server as I have demonstrated at home.

Why does it work in one but not the other when both versions of SSMS are the same?
Does anyone have an alternative to convert the date to the required format?

Thanks for your help

Vinnie

what date type is updatedate defined as? also error could be due to version of your sql server not ssms?

DECLARE @d DATE = '2005-07-01';
DECLARE @dVarchar varchar(10) = '2005-07-01';
SELECT FORMAT(@d, 'MMM-yy') AS Audit_Month, 
       format(cast(@dVarchar as date), 'MMM-yy') as _if_date_is_string,
	   DATENAME(month, @d) as _month,
	   year(@d) _year

The version of SSMS you're using is not important.
Do the two SQL Servers have the same version?

Have you tried

FORMAT(su.updatedate, 'MMM-yy', 'en-us')

or one of its variants?

An alternative:

SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 13), 4, 8) AS [Mon YY]

Just use the old method:-

select left(datename(month,su.updatedate),3) + '-' + convert(varchar(4),right(year(su.updatedate),2)) as  Audit_Month
FROM Sales.Update su

You can get this using:

Select concat(left(datename(month, su.updatedate), 3), '-', right(datename(year, su.updatedate), 2))

Or - if CONCAT doesn't work because of a version issue:

Select left(datename(month, su.updatedate), 3) + '-' + right(datename(year, su.updatedate), 2)

Thank you all so much for your responses. I have established that although the version of SSMS is the same the server versions are different which explains why the FORMAT function would not work.

There is clearly more than one way to get the desired outcome so difficult to pick a best answer.

Many thanks for your input/assistance

Vinnie

It depends. The less casting formatting you do from one data type to another the better.
Is this for reporting purposes or application?

It is for reporting

Are you hitting the oltp (Online Transactional Processing) tables for this reporting?

It has been proven on many posts across the internet that the FORMAT function in T-SQL is a good 44 times SLOWER than CONVERT. The idea of poking at a couple of the established formats is much more performant. Reducing the number of concatenations will make it faster still.

With that, I'll also throw my hat into the ring with the following code, which should do just fine.and fast if you're in countries that use a 3 letter abbreviation for months..

 SELECT STUFF(CONVERT(CHAR(10),GETDATE(),7),4,5,'-');

If you expect your code to be used in countries where the month abbreviations vary in length or you want to make your code more bullet, then consider using the following, which is a little bit slower but still -proofhead, shoulders, and waist above FORMAT when it comes to performance.

SELECT REPLACE(SUBSTRING(CONVERT(NVARCHAR(30),GETDATE(),6) COLLATE Latin1_General_BIN ,4,30),N' ',N'-');

{EDIT} Highlighted a warning, added a tip, and added binary collation to the code immediately above to almost triple the speed.

Wouldn't a dedicated reporting db be the better approach?
Calendar table with all the variations for each locale? Then FKey to this calendar table? Or if a flat file just stuff that into a dedicated column

It already exists. What to you think powers the CONVERT function? Look at the sys.syslanguages table and see. The cool part about using the functions is that they auto-magically adjust with whatever the language for the server is setup to be.

What I am talking about is if you deal with a multi language system and have reports consumed by different people from different date styles. Doing this on the fly against oltp sounds expensive

Yep... understood. That's the problem with translations and formatting for display... no matter what you do, it's going to be relatively expensive.

So which would be faster; a lookup against a system table using functions designed to take advantage of a translation or a homegrown system that's designed to do a straight lookup? I've never had to do such a formatted translation of month abbreviations before and it deserves a test if someone needs to do such a thing.

If you'll provide the code to create and populate the calendar table you speak of, I'll be happy to set up a demonstrable repeatable test harness for the comparison.

1 Like

TAKE 1 --

DateDimension Table I got from some website. Pretty sure will need to some vetting


DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 30;

-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals
--SET LANGUAGE Italian;

SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE us_english;

DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

IF OBJECT_ID('tempdb..#dim') IS NOT NULL 
begin
	DROP TABLE #dim
end


if exists(select 1 from sys.tables where name = 'DateDimension')
	begin
		drop table dbo.DateDimension
	end
	

CREATE TABLE dbo.DateDimension
(
  --DateKey           INT         NOT NULL PRIMARY KEY,
  [Date]              DATE        NOT NULL,
  [Day]               TINYINT     NOT NULL,
  --DaySuffix           CHAR(2)     NOT NULL,
  [Weekday]           TINYINT     NOT NULL,
  WeekDayName         VARCHAR(10) NOT NULL,
  IsWeekend           BIT         NOT NULL,
  IsHoliday           BIT         NOT NULL,
  HolidayText         VARCHAR(64) SPARSE,
  DOWInMonth          TINYINT     NOT NULL,
  [DayOfYear]         SMALLINT    NOT NULL,
  WeekOfMonth         TINYINT     NOT NULL,
  WeekOfYear          TINYINT     NOT NULL,
  ISOWeekOfYear       TINYINT     NOT NULL,
  [Month]             TINYINT     NOT NULL,
  [MonthName]         VARCHAR(10) NOT NULL,
  [Quarter]           TINYINT     NOT NULL,
  --QuarterName         VARCHAR(6)  NOT NULL,
  [Year]              INT         NOT NULL,
  MMYYYY              CHAR(6)     NOT NULL,
  MonthYear           CHAR(7)     NOT NULL,
  FirstDayOfMonth     DATE        NOT NULL,
  LastDayOfMonth      DATE        NOT NULL,
  FirstDayOfQuarter   DATE        NOT NULL,
  LastDayOfQuarter    DATE        NOT NULL,
  FirstDayOfYear      DATE        NOT NULL,
  LastDayOfYear       DATE        NOT NULL,
  FirstDayOfNextMonth DATE        NOT NULL,
  FirstDayOfNextYear  DATE        NOT NULL,
  LanguageCode        nvarchar(10) NOT NULL,
  ReportDate nvarchar(6) not null
);

CREATE NONCLUSTERED INDEX [NCI_DateDimension] ON [dbo].DateDimension
(
	LanguageCode ASC
) include ([date],ReportDate) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]



--this is just a holding table for intermediate calculations:

CREATE TABLE #dim
(
  [date]       DATE PRIMARY KEY, 
  [day]        AS DATEPART(DAY,      [date]),
  [month]      AS DATEPART(MONTH,    [date]),
  FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
  [MonthName]  AS DATENAME(MONTH,    [date]),
  [week]       AS DATEPART(WEEK,     [date]),
  [ISOweek]    AS DATEPART(ISO_WEEK, [date]),
  [DayOfWeek]  AS DATEPART(WEEKDAY,  [date]),
  [quarter]    AS DATEPART(QUARTER,  [date]),
  [year]       AS DATEPART(YEAR,     [date]),
  FirstOfYear  AS CONVERT(DATE, DATEADD(YEAR,  DATEDIFF(YEAR,  0, [date]), 0)),
  Style112     AS CONVERT(CHAR(8),   [date], 112),
  Style101     AS CONVERT(CHAR(10),  [date], 101)
);

-- use the catalog views to generate as many rows as we need

INSERT #dim([date]) 
SELECT d
FROM
(
  SELECT d = DATEADD(DAY, rn - 1, @StartDate)
  FROM 
  (
    SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) 
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    -- on my system this would support > 5 million days
    ORDER BY s1.[object_id]
  ) AS x
) AS y;


INSERT dbo.DateDimension WITH (TABLOCKX)
SELECT
  --DateKey     = CONVERT(INT, Style112),
  [Date]        = [date],
  [Day]         = CONVERT(TINYINT, [day]),
  --DaySuffix     = CONVERT(CHAR(2), CASE WHEN [day] / 10 = 1 THEN 'th' ELSE 
  --                CASE RIGHT([day], 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
	 --             WHEN '3' THEN 'rd' ELSE 'th' END END),
  [Weekday]     = CONVERT(TINYINT, [DayOfWeek]),
  [WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
  [IsWeekend]   = CONVERT(BIT, CASE WHEN [DayOfWeek] IN (1,7) THEN 1 ELSE 0 END),
  [IsHoliday]   = CONVERT(BIT, 0),
  HolidayText   = CONVERT(VARCHAR(64), NULL),
  [DOWInMonth]  = CONVERT(TINYINT, ROW_NUMBER() OVER 
                  (PARTITION BY FirstOfMonth, [DayOfWeek] ORDER BY [date])),
  [DayOfYear]   = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),
  WeekOfMonth   = CONVERT(TINYINT, DENSE_RANK() OVER 
                  (PARTITION BY [year], [month] ORDER BY [week])),
  WeekOfYear    = CONVERT(TINYINT, [week]),
  ISOWeekOfYear = CONVERT(TINYINT, ISOWeek),
  [Month]       = CONVERT(TINYINT, [month]),
  [MonthName]   = CONVERT(VARCHAR(10), [MonthName]),
  [Quarter]     = CONVERT(TINYINT, [quarter]),
  --QuarterName   = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First' 
  --                WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END), 
  [Year]        = [year],
  MMYYYY        = CONVERT(CHAR(6), LEFT(Style101, 2)    + LEFT(Style112, 4)),
  MonthYear     = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),
  FirstDayOfMonth     = FirstOfMonth,
  LastDayOfMonth      = MAX([date]) OVER (PARTITION BY [year], [month]),
  FirstDayOfQuarter   = MIN([date]) OVER (PARTITION BY [year], [quarter]),
  LastDayOfQuarter    = MAX([date]) OVER (PARTITION BY [year], [quarter]),
  FirstDayOfYear      = FirstOfYear,
  LastDayOfYear       = MAX([date]) OVER (PARTITION BY [year]),
  FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
  FirstDayOfNextYear  = DATEADD(YEAR,  1, FirstOfYear),
  'en-US' as LanguageCode,
  ReportDate = FORMAT([date], 'MMM-yy')
FROM #dim
OPTION (MAXDOP 1);

--Jul-05
SET LANGUAGE Italian;
INSERT dbo.DateDimension WITH (TABLOCKX)
SELECT
  --DateKey     = CONVERT(INT, Style112),
  [Date]        = [date],
  [Day]         = CONVERT(TINYINT, [day]),
  --DaySuffix     = CONVERT(CHAR(2), CASE WHEN [day] / 10 = 1 THEN 'th' ELSE 
  --                CASE RIGHT([day], 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
	 --             WHEN '3' THEN 'rd' ELSE 'th' END END),
  [Weekday]     = CONVERT(TINYINT, [DayOfWeek]),
  [WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
  [IsWeekend]   = CONVERT(BIT, CASE WHEN [DayOfWeek] IN (1,7) THEN 1 ELSE 0 END),
  [IsHoliday]   = CONVERT(BIT, 0),
  HolidayText   = CONVERT(VARCHAR(64), NULL),
  [DOWInMonth]  = CONVERT(TINYINT, ROW_NUMBER() OVER 
                  (PARTITION BY FirstOfMonth, [DayOfWeek] ORDER BY [date])),
  [DayOfYear]   = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),
  WeekOfMonth   = CONVERT(TINYINT, DENSE_RANK() OVER 
                  (PARTITION BY [year], [month] ORDER BY [week])),
  WeekOfYear    = CONVERT(TINYINT, [week]),
  ISOWeekOfYear = CONVERT(TINYINT, ISOWeek),
  [Month]       = CONVERT(TINYINT, [month]),
  [MonthName]   = CONVERT(VARCHAR(10), [MonthName]),
  [Quarter]     = CONVERT(TINYINT, [quarter]),
  --QuarterName   = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First' 
  --                WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END), 
  [Year]        = [year],
  MMYYYY        = CONVERT(CHAR(6), LEFT(Style101, 2)    + LEFT(Style112, 4)),
  MonthYear     = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),
  FirstDayOfMonth     = FirstOfMonth,
  LastDayOfMonth      = MAX([date]) OVER (PARTITION BY [year], [month]),
  FirstDayOfQuarter   = MIN([date]) OVER (PARTITION BY [year], [quarter]),
  LastDayOfQuarter    = MAX([date]) OVER (PARTITION BY [year], [quarter]),
  FirstDayOfYear      = FirstOfYear,
  LastDayOfYear       = MAX([date]) OVER (PARTITION BY [year]),
  FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
  FirstDayOfNextYear  = DATEADD(YEAR,  1, FirstOfYear),
  'it-IT' as LanguageCode,
  ReportDate = FORMAT([date], 'MMM-yy')
FROM #dim
OPTION (MAXDOP 1);

Then ran these 3 queries with query plans on


SELECT STUFF(CONVERT(CHAR(10),dd.DATE,7),4,5,'-')
  from DateDimension dd
  where LanguageCode = 'it-IT'

SELECT FORMAT(dd.DATE, 'MMM-yy', 'it-it')
  from DateDimension dd
  where LanguageCode = 'it-IT'

SELECT ReportDate
  from DateDimension dd
  where LanguageCode = 'it-IT'

looks very negligible?

The other glaring issue is

STUFF(CONVERT(CHAR(10),dd.DATE,7),4,5,'-') bypasses the currently selected language and forces English.

Thanks, Tito. I had to remove the "WITH" option on the index creation because it contained a 2019 option. Then I built the table.

I'd be interested in where you got this code (or did you write it?) because 1) no matter what your query is, it will do a SCAN of 10,958 rows even if you just search for one date and no other columns. It's because the author made the language the leading column instead of the date in the only index applied to the table (so far... like you said, it needs some vetting).

Here's the code for what I'm talking about above. It does a seek to the beginning of a language segment in the table and then it scans the rows. Not efficient at all.

--===== This looks like a SEEK but... it's really a SCAN of 10,958 Rows
 SELECT [Date]
   FROM dbo.DateDimension
  WHERE [Date] = CONVERT(DATE,GETDATE())
    AND LanguageCode = 'en-US'
;

Adding an NCI for Date and Language will cause a true SEEK but also be performance challenged because it would need to do a row lookup based on RID if you included any other columns for a return. That's not so bad for singleton lookups but would be pretty bad for ranges of dates to be looked up.

On the part about the following code having the glaring issue of bypassing current language settings, I agree and said so in the post where I listed it. I probably shouldn't have posted it even though I included the warning. I'll leave it out of the tests because it's useless in a multilingual environment.

SELECT STUFF(CONVERT(CHAR(10),dd.DATE,7),4,5,'-')
  from DateDimension dd
  where LanguageCode = 'it-IT'

;

Another issue with using a Calendar table is that it seems that no one actually builds them with the idea of creating SARGable queries for date lookups. You have to do the lookups in this table by a "whole" date with no time. That means that if you have wisely chosen to keep date and time together in a single column in some table, you would have to wrap the reference to that column in a formula that strips the time.

I'll be back later with some tests and test results.

1 Like

this is where I found it Sir Moden :wink:,
a. the index is my cheat to quickly get some performance because it kept complaining.
b. LanguageCode is also my creation.

This also has issues with non English languages
SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR(30),GETDATE(),6),4,30),' ','-');
As always open to any and all feedback.

What kind of PK or key column index would it benefit from to be blazing fast aka SARGable?

I went all the way back to 1990 as start date and pushed it 300 years. but I did not see that much performance difference specifically the compute scalar Cost: 2% I believe that is the part it calls sql's functions.

It does, indeed. That's because I forgot that it had to all be NVARCHAR(). If you change it to ...
SELECT REPLACE(SUBSTRING(CONVERT(NVARCHAR(30),GETDATE(),6),4,30),N' ',N'-');

... things work as expected instead of giving you a bunch of "?" marks.

Still testing. Interesting stuff that I'm finding.

Ok... here we go.

First, I had to change a couple of things in the original code that you provided to build the DateDimension table. For example , your ReportDate column was set to NVARCHAR(6), which isn't big enough to handle some countries month abbreviations. I changed it to NARCHAR(30).

Also, the index your created was in the right vein but it only supported a SEEK followed by a range SCAN of all rows in a given language. So, in reality, it was always SCANing almost 11,000 rows every query. Now, that's not a huge problem but it is a wasteful thing. You also had nothing to guarantee that each language only had 1 and only 1 copy of any given date in it. Further, there was nothing to handle a contingency if someone went search for another column that didn't have an NCI on it.

So, I added a Clustered PK that would solve all 3 of those problems.

I didn't, however, take the time to fix the rather random use of CASING in all of the column names. I left them all as they were. In real life, I'd have standardized the casing.

Here's the copy of your code with the slight modifications I added above.

WARNING!!! WARNING!!! WARNING!!! WARNING!!! WARNING!!! WARNING!!! WARNING!!!
THIS CODE UNCONDITIONALLY DROPS A TABLE NAMED DBO.DATEDIMENSION!!!
WARNING!!! WARNING!!! WARNING!!! WARNING!!! WARNING!!! WARNING!!! WARNING!!!

DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 30;

-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals
SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE us_english;

DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

--===== If the new table already exists, drop it(Changed this)
     IF OBJECT_ID('tempdb..#Dim','U') IS NOT NULL
   DROP TABLE #Dim
;
--===== If the new table already exists, drop it(Changed this)
     IF OBJECT_ID('dbo.DateDimension','U') IS NOT NULL
   DROP TABLE dbo.DateDimension
;
CREATE TABLE dbo.DateDimension
(
  --DateKey           INT         NOT NULL PRIMARY KEY,
  [Date]              DATE        NOT NULL,
  [Day]               TINYINT     NOT NULL,
  --DaySuffix           CHAR(2)     NOT NULL,
  [Weekday]           TINYINT     NOT NULL,
  WeekDayName         VARCHAR(10) NOT NULL,
  IsWeekend           BIT         NOT NULL,
  IsHoliday           BIT         NOT NULL,
  HolidayText         VARCHAR(64) SPARSE,
  DOWInMonth          TINYINT     NOT NULL,
  [DayOfYear]         SMALLINT    NOT NULL,
  WeekOfMonth         TINYINT     NOT NULL,
  WeekOfYear          TINYINT     NOT NULL,
  ISOWeekOfYear       TINYINT     NOT NULL,
  [Month]             TINYINT     NOT NULL,
  [MonthName]         VARCHAR(10) NOT NULL,
  [Quarter]           TINYINT     NOT NULL,
  --QuarterName         VARCHAR(6)  NOT NULL,
  [Year]              INT         NOT NULL,
  MMYYYY              CHAR(6)     NOT NULL,
  MonthYear           CHAR(7)     NOT NULL,
  FirstDayOfMonth     DATE        NOT NULL,
  LastDayOfMonth      DATE        NOT NULL,
  FirstDayOfQuarter   DATE        NOT NULL,
  LastDayOfQuarter    DATE        NOT NULL,
  FirstDayOfYear      DATE        NOT NULL,
  LastDayOfYear       DATE        NOT NULL,
  FirstDayOfNextMonth DATE        NOT NULL,
  FirstDayOfNextYear  DATE        NOT NULL,
  LanguageCode        NVARCHAR(30) NOT NULL,
  ReportDate nvarchar(30) not null --changed this from 6 to 30
)
;
--===== Removed this index because, although it did a SEEK, it was
     -- also doing a SCAN of the more than 10K Rows for each language.
--CREATE NONCLUSTERED INDEX [NCI_DateDimension] ON [dbo].DateDimension
--(
--	LanguageCode ASC
--) include ([date],ReportDate) 
--;
--===== Replaced the above index with a real Clustered PK that will handle
     -- the odd lookups without having to build a special Non-Clustered Index
     -- to handle them.  This also guarantees only 1 date per language.
  ALTER TABLE [dbo].DateDimension
    ADD CONSTRAINT PK_DateDimension PRIMARY KEY CLUSTERED (LanguageCode,[date])
;

--this is just a holding table for intermediate calculations:
CREATE TABLE #dim
(
  [date]       DATE PRIMARY KEY CLUSTERED, 
  [day]        AS DATEPART(DAY,      [date]),
  [month]      AS DATEPART(MONTH,    [date]),
  FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
  [MonthName]  AS DATENAME(MONTH,    [date]),
  [week]       AS DATEPART(WEEK,     [date]),
  [ISOweek]    AS DATEPART(ISO_WEEK, [date]),
  [DayOfWeek]  AS DATEPART(WEEKDAY,  [date]),
  [quarter]    AS DATEPART(QUARTER,  [date]),
  [year]       AS DATEPART(YEAR,     [date]),
  FirstOfYear  AS CONVERT(DATE, DATEADD(YEAR,  DATEDIFF(YEAR,  0, [date]), 0)),
  Style112     AS CONVERT(CHAR(8),   [date], 112),
  Style101     AS CONVERT(CHAR(10),  [date], 101)
)
;

-- use the catalog views to generate as many rows as we need
INSERT #dim([date]) 
SELECT d
FROM
(
  SELECT d = DATEADD(DAY, rn - 1, @StartDate)
  FROM 
  (
    SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) 
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1 
    CROSS JOIN sys.all_objects AS s2
    -- on my system this would support > 5 million days
    ORDER BY s1.[object_id]
  ) AS x
) AS y;


INSERT dbo.DateDimension WITH (TABLOCKX)
SELECT
  --DateKey     = CONVERT(INT, Style112),
  [Date]        = [date],
  [Day]         = CONVERT(TINYINT, [day]),
  --DaySuffix     = CONVERT(CHAR(2), CASE WHEN [day] / 10 = 1 THEN 'th' ELSE 
  --                CASE RIGHT([day], 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
	 --             WHEN '3' THEN 'rd' ELSE 'th' END END),
  [Weekday]     = CONVERT(TINYINT, [DayOfWeek]),
  [WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
  [IsWeekend]   = CONVERT(BIT, CASE WHEN [DayOfWeek] IN (1,7) THEN 1 ELSE 0 END),
  [IsHoliday]   = CONVERT(BIT, 0),
  HolidayText   = CONVERT(VARCHAR(64), NULL),
  [DOWInMonth]  = CONVERT(TINYINT, ROW_NUMBER() OVER 
                  (PARTITION BY FirstOfMonth, [DayOfWeek] ORDER BY [date])),
  [DayOfYear]   = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),
  WeekOfMonth   = CONVERT(TINYINT, DENSE_RANK() OVER 
                  (PARTITION BY [year], [month] ORDER BY [week])),
  WeekOfYear    = CONVERT(TINYINT, [week]),
  ISOWeekOfYear = CONVERT(TINYINT, ISOWeek),
  [Month]       = CONVERT(TINYINT, [month]),
  [MonthName]   = CONVERT(VARCHAR(10), [MonthName]),
  [Quarter]     = CONVERT(TINYINT, [quarter]),
  --QuarterName   = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First' 
  --                WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END), 
  [Year]        = [year],
  MMYYYY        = CONVERT(CHAR(6), LEFT(Style101, 2)    + LEFT(Style112, 4)),
  MonthYear     = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),
  FirstDayOfMonth     = FirstOfMonth,
  LastDayOfMonth      = MAX([date]) OVER (PARTITION BY [year], [month]),
  FirstDayOfQuarter   = MIN([date]) OVER (PARTITION BY [year], [quarter]),
  LastDayOfQuarter    = MAX([date]) OVER (PARTITION BY [year], [quarter]),
  FirstDayOfYear      = FirstOfYear,
  LastDayOfYear       = MAX([date]) OVER (PARTITION BY [year]),
  FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
  FirstDayOfNextYear  = DATEADD(YEAR,  1, FirstOfYear),
  'en-US' as LanguageCode,
  ReportDate = FORMAT([date], 'MMM-yy')
FROM #dim
OPTION (MAXDOP 1);

--Jul-05
SET LANGUAGE Italian;
INSERT dbo.DateDimension WITH (TABLOCKX)
SELECT
  --DateKey     = CONVERT(INT, Style112),
  [Date]        = [date],
  [Day]         = CONVERT(TINYINT, [day]),
  --DaySuffix     = CONVERT(CHAR(2), CASE WHEN [day] / 10 = 1 THEN 'th' ELSE 
  --                CASE RIGHT([day], 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
	 --             WHEN '3' THEN 'rd' ELSE 'th' END END),
  [Weekday]     = CONVERT(TINYINT, [DayOfWeek]),
  [WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
  [IsWeekend]   = CONVERT(BIT, CASE WHEN [DayOfWeek] IN (1,7) THEN 1 ELSE 0 END),
  [IsHoliday]   = CONVERT(BIT, 0),
  HolidayText   = CONVERT(VARCHAR(64), NULL),
  [DOWInMonth]  = CONVERT(TINYINT, ROW_NUMBER() OVER 
                  (PARTITION BY FirstOfMonth, [DayOfWeek] ORDER BY [date])),
  [DayOfYear]   = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),
  WeekOfMonth   = CONVERT(TINYINT, DENSE_RANK() OVER 
                  (PARTITION BY [year], [month] ORDER BY [week])),
  WeekOfYear    = CONVERT(TINYINT, [week]),
  ISOWeekOfYear = CONVERT(TINYINT, ISOWeek),
  [Month]       = CONVERT(TINYINT, [month]),
  [MonthName]   = CONVERT(VARCHAR(10), [MonthName]),
  [Quarter]     = CONVERT(TINYINT, [quarter]),
  --QuarterName   = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First' 
  --                WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END), 
  [Year]        = [year],
  MMYYYY        = CONVERT(CHAR(6), LEFT(Style101, 2)    + LEFT(Style112, 4)),
  MonthYear     = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),
  FirstDayOfMonth     = FirstOfMonth,
  LastDayOfMonth      = MAX([date]) OVER (PARTITION BY [year], [month]),
  FirstDayOfQuarter   = MIN([date]) OVER (PARTITION BY [year], [quarter]),
  LastDayOfQuarter    = MAX([date]) OVER (PARTITION BY [year], [quarter]),
  FirstDayOfYear      = FirstOfYear,
  LastDayOfYear       = MAX([date]) OVER (PARTITION BY [year]),
  FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
  FirstDayOfNextYear  = DATEADD(YEAR,  1, FirstOfYear),
  'it-IT' as LanguageCode,
  ReportDate = FORMAT([date], 'MMM-yy')
FROM #dim
OPTION (MAXDOP 1);

--===== Houskeeping
     -- These settings are session sensitive so you don't
     -- normally need to do this.
    SET LANGUAGE us_english; --Added this
   DROP TABLE #Dim           --Added this
;

You tested with a little over 10,000 rows in the dbo.DateDimension table and that also didn't require a JOIN anywhere. With that in mind, the following builds a 1 column table containing 10 Million random dates (a small table by today's standards). Since it only has a single non-unique column, I only added a Non Clustered Index to it.

Here's the code...

--===== Presets
    SET LANGUAGE us_english;
     IF OBJECT_ID('tempdb..#Dates','U') IS NOT NULL
   DROP TABLE #Dates
;
--===== Create a large but narrow test table containing random DATETIME/s over the 10 year period of
     -- Jan 2015 up to and not including Jan 2025.
 SELECT TOP 10000000  --Yeah... 10 Million.  That's small in today's terms.
        SomeDate = ISNULL(RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'Jan 2015','Jan 2025')+CONVERT(DATETIME,'Jan 2015'),0)
   INTO #Dates
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
--===== Index the dae column like you would on a real table.
     -- There are no columns to INCLUDE on this particular table.
     -- we're keeping it real simple.
 CREATE INDEX IX_#Dates_Date ON #Dates (SomeDate)
;

Now for some testing. I did "english-US" testing first, just to set a baseline. I've got several tests and they include the code we've corrected together for the strictly inline calculations. I also added one extra test to show just how damned expensive it is to add a dash if you don't know about COLLATION in conjunction with the use of REPLACE.

Here's the code for the "English_US" version. Each section of the code clears proc and buffer cache and then runs each section 5 times. This allows to see if there are any anomalies and determine if there's a significant penalty on the first uncached run of each section.

What we're doing is looking up one full year (2019) in the #Dates table and then coming up with the "Reporting Dates". One full year contains approximately 1/10th of the ten year range, so we're working with approximately 1 Million rows out of the 10 (kind of like real life) In the case of my runs below, the count was actually 999,511 rows.

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
GO

SET LANGUAGE us_english; --====================================================
--SET LANGUAGE Croatian; --======================================================
GO
--=============================================================================
     -- Clear proc and buffer cache
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
GO
--===== FORMAT ================================================================
DECLARE @BitBucket NVARCHAR(30);
 SELECT @BitBucket = FORMAT(tt.SomeDate, 'MMM-yy', 'en-US')
   FROM #Dates tt
  WHERE tt.SomeDate >= '20190101'
    AND tt.SomeDate <  '20200101'
;
GO 5
--=============================================================================
     -- Clear proc and buffer cache
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
GO
--===== CALENDAR TABLE ========================================================
SET STATISTICS TIME,IO off
DECLARE @BitBucket NVARCHAR(30);
 SELECT @BitBucket = ReportDate
   FROM dbo.DateDimension dd
   JOIN #Dates            tt ON dd.[Date] = CONVERT(DATE,tt.SomeDate) --Required to match
  WHERE dd.[Date]       >= '20190101' --This is required to avoid a large 10K row scan
    AND dd.[Date]       <  '20200101' --This is required to avoid a large 10K row scan
    AND dd.LanguageCode  = 'en-US'
    AND tt.SomeDate     >= '20190101'
    AND tt.SomeDate     <  '20200101'
;
GO 5
--=============================================================================
     -- Clear proc and buffer cache
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
GO
--===== INLINE FORMULA WITHOUT BINARY COLLATION ===============================
DECLARE @BitBucket NVARCHAR(30);
 SELECT @BitBucket = REPLACE(SUBSTRING(CONVERT(NVARCHAR(30),tt.SomeDate,6),4,30),N' ',N'-')
   FROM #Dates tt
  WHERE tt.SomeDate >= '20190101'
    AND tt.SomeDate <  '20200101'
;
GO 5
--=============================================================================
     -- Clear proc and buffer cache
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
GO
--===== INLINE FORMULA WITH BINARY COLLATION ==================================
DECLARE @BitBucket NVARCHAR(30);
 SELECT @BitBucket = REPLACE(SUBSTRING(CONVERT(NVARCHAR(30),tt.SomeDate,6) COLLATE Latin1_General_BIN ,4,30),N' ',N'-') 
   FROM #Dates tt
  WHERE tt.SomeDate >= '20190101'
    AND tt.SomeDate <  '20200101'
;
GO 5
--=============================================================================
     -- Clear proc and buffer cache
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
GO
--===== INLINE FORMULA WITHOUT THE DASH =======================================
DECLARE @BitBucket NVARCHAR(30);
 SELECT @BitBucket = SUBSTRING(CONVERT(NVARCHAR(30),tt.SomeDate,6),4,30)
   FROM #Dates tt
  WHERE tt.SomeDate >= '20190101'
    AND tt.SomeDate <  '20200101'
;
GO 5
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
GO 

Since the Execution Plan (even the supposedly "Actual" Execution Plan") has a shedload of estimates in it, I never use it as the final determination as to what the better code is. So, here's the Profiler Run I had running against the SPID I was using. There's a lot of good information to be derived from this but, most painfully obvious, is that FORMAT is bad for performance. I won't use it even on small rowsets because, if I did, it would become a piece of the "Death by a thousand cuts" that I've been trying to remove from my production databases for the last 8 years (and I'm still not done and long story about why it appears that I'm chasing milliseconds).

Here's the output from the SQL Profiler run. And be sure to notice what the use of COLLATE did for us as well as simply not adding a bloody dash to the MMM-YY format.

There are 3 obvious winners there and, of the 3, getting rid of the dash was the best of all.

Here's the same code with the minor culture and language changes for Italian...

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
GO
SET LANGUAGE Italian; --=======================================================
--SET LANGUAGE us_english; --====================================================
--SET LANGUAGE Croatian; --======================================================
GO
--=============================================================================
     -- Clear proc and buffer cache
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
GO
--===== FORMAT ================================================================
DECLARE @BitBucket NVARCHAR(30);
 SELECT @BitBucket = FORMAT(tt.SomeDate, 'MMM-yy', 'it_IT')
   FROM #Dates tt
  WHERE tt.SomeDate >= '20190101'
    AND tt.SomeDate <  '20200101'
;
GO 5
--=============================================================================
     -- Clear proc and buffer cache
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
GO
--===== CALENDAR TABLE ========================================================
SET STATISTICS TIME,IO off
DECLARE @BitBucket NVARCHAR(30);
 SELECT @BitBucket = ReportDate
   FROM dbo.DateDimension dd
   JOIN #Dates            tt ON dd.[Date] = CONVERT(DATE,tt.SomeDate) --Required to match
  WHERE dd.[Date]       >= '20190101' --This is required to avoid a large 10K row scan
    AND dd.[Date]       <  '20200101' --This is required to avoid a large 10K row scan
    AND dd.LanguageCode  = 'it-IT'
    AND tt.SomeDate     >= '20190101'
    AND tt.SomeDate     <  '20200101'
;
GO 5
--=============================================================================
     -- Clear proc and buffer cache
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
GO
--===== INLINE FORMULA WITHOUT BINARY COLLATION ===============================
DECLARE @BitBucket NVARCHAR(30);
 SELECT @BitBucket = REPLACE(SUBSTRING(CONVERT(NVARCHAR(30),tt.SomeDate,6),4,30),N' ',N'-')
   FROM #Dates tt
  WHERE tt.SomeDate >= '20190101'
    AND tt.SomeDate <  '20200101'
;
GO 5
--=============================================================================
     -- Clear proc and buffer cache
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
GO
--===== INLINE FORMULA WITH BINARY COLLATION ==================================
DECLARE @BitBucket NVARCHAR(30);
 SELECT @BitBucket = REPLACE(SUBSTRING(CONVERT(NVARCHAR(30),tt.SomeDate,6) COLLATE Latin1_General_BIN ,4,30),N' ',N'-') 
   FROM #Dates tt
  WHERE tt.SomeDate >= '20190101'
    AND tt.SomeDate <  '20200101'
;
GO 5
--=============================================================================
     -- Clear proc and buffer cache
   DBCC FREEPROCCACHE;
   DBCC DROPCLEANBUFFERS;
GO
--===== INLINE FORMULA WITHOUT THE DASH =======================================
DECLARE @BitBucket NVARCHAR(30);
 SELECT @BitBucket = SUBSTRING(CONVERT(NVARCHAR(30),tt.SomeDate,6),4,30)
   FROM #Dates tt
  WHERE tt.SomeDate >= '20190101'
    AND tt.SomeDate <  '20200101'
;
GO 5
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
GO

Here's the profiler run from that... no surprises...

I also did the Finnish language to make sure that their longer abbreviations were coming up correct in the formulas. They did. I didn't take the time to create rows in the dbo.DateDimension table for Finnish and so couldn't run that test for the Finnish Language. I'll also tell you that the FORMAT function took an extra 400ms for the Finnish Language. The formula methods actually didn't take any extra time.

And all of that brings up a couple of good points.

  1. If your Calendar Table (DateDimension table, whatever) doesn't support a given language, you're dead in the water until it has been made to do so. The formulas don't have that problem.
  2. FORMAT is handy if you're in one language and need to format dates in another without changing languages (as is the Calendar Table) but ("It Depends") it may not be worth all the extra duration and CPU. Like I said, 10 million rows is nothing today and we only looked up about a million. It would take 85-90 seconds to process 10 million rows with FORMAT where the formulas and Calendar Table would each do it in less than 3 seconds.
  3. I won't even use FORMAT for little stuff until they fix its performance.
  4. You have to remember to add date range limits to the calendar side of the criteria or you're going to be scanning almost 11K rows for the given language instead of just 365 for a given year. It won't cost you much unless you're doing thousands of lookups per hour... and you'd better plan on that happening.
  5. Formulas ROCK! :smiley: :smiley: :smiley:
1 Like

:scream: :scream: :scream: :scream: This is scary amazing!!!!
I would love to see the actual values of the inline formula. If you are in Finnish and they show English then toss that puppy out.

Thanks very much @JeffModen. This should be done as an article all its own!

Also not sure if it helps but I also tinkered around with bitfield for the languageCode column.

LanguageCode        bigint NOT NULL,


 2^1 as LanguageCode, --english, really it is arbitrary

LanguageCode = 2^2 , --Italian

WHERE (LanguageCode)2^2 = @languagecode

I will try this with other non-Roman script languages and see what I can come up with.

Man I bet you had a lot of fun with this (I am jealous!). This is unreal!