January 25, 2016 at 9:13 am
spaghettidba (1/25/2016)
Eirikur Eiriksson (1/25/2016)
spaghettidba (1/25/2016)
Eirikur Eiriksson (1/25/2016)
spaghettidba (1/25/2016)
SQL Server 2012 supports the FORMAT function:
select format(GETDATE(), 'dd-MMM-YYYY')
Gianluca, don't use the format function, it has terrible performance.
😎
It is a CLR function, so there's a startup cost and it performs slower than native functions.
It has the advantage of being simple and easy to understand in your code.
If I'm worrying about the performance of a function I use to format my output, it must be something that really runs like hell on the rest of the execution (reading data from tables) or returns a huge amount of rows. Most of the time it is not so.
Both I and Jeff Moden have posted examples showing the problem with the format function, 40-50 times slower than alternatives in most cases.
😎
No doubt about that.
My point is: does it really matter when I'm formatting 30 or even 100 dates?
I know what you are saying but when you have most millions of those running every hour it really starts to make an impact.
😎
January 25, 2016 at 9:17 am
ben.brugman (1/25/2016)
Luis Cazares (1/25/2016)
Another option:
SELECT UPPER( REPLACE( CONVERT(char(11), GETDATE(), 106), ' ', '-'))Be carefull with dates, very often the language or the regional settings are important.
For the solution of Luis (our Italian Plumber) :
set language italian
SELECT UPPER( REPLACE( CONVERT(char(11), GETDATE(), 106), ' ', '-')) , 'Which Month ?'
This would result in :
L'impostazione della lingua è stata sostituita con Italiano.
----------------------------------- -------------
25-GEN-2016 Which Month ?
(1 row(s) affected)
Assuming the the language and regional settings are always the same can lead to problems.
Ben
How can the code return incorrect results, Ben? Am I missing something here?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 25, 2016 at 9:30 am
spaghettidba (1/25/2016)
Eirikur Eiriksson (1/25/2016)
spaghettidba (1/25/2016)
Eirikur Eiriksson (1/25/2016)
spaghettidba (1/25/2016)
SQL Server 2012 supports the FORMAT function:
select format(GETDATE(), 'dd-MMM-YYYY')
Gianluca, don't use the format function, it has terrible performance.
😎
It is a CLR function, so there's a startup cost and it performs slower than native functions.
It has the advantage of being simple and easy to understand in your code.
If I'm worrying about the performance of a function I use to format my output, it must be something that really runs like hell on the rest of the execution (reading data from tables) or returns a huge amount of rows. Most of the time it is not so.
Both I and Jeff Moden have posted examples showing the problem with the format function, 40-50 times slower than alternatives in most cases.
😎
No doubt about that.
My point is: does it really matter when I'm formatting 30 or even 100 dates?
Yes. Not because of the low quantity but because someone else in search of code may find it and use it on something larger or something that's called several 10's of thousands of times per hour. "Take care of the pennies and the dollars will mind themselves" because even the small stuff adds to the overall problems of performance and resource usage.
In this particular case, FORMAT is more than 180 times slower. It's really nasty. Little things like this do add up.
--===== If the test table already exists,
-- drop it to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
;
--===== Create a million random dates as the DATETIME
-- datatype for the 2010 decade and store them in
-- a Temp Table. This only takes a second.
SELECT TOP 1000000
SomeDate = DATEADD(dd,
ABS(CHECKSUM(NEWID()))
% DATEDIFF(dd,'2010','2020'),
'2010')
INTO #MyHead
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
PRINT LEFT('--===== CONVERT dd-MMM-YYYY '+REPLICATE('=',100), 100);
SET STATISTICS TIME ON;
DECLARE @Bitbucket CHAR(11);
SELECT @Bitbucket = UPPER( REPLACE( CONVERT(char(11), GETDATE(), 106), ' ', '-'))
FROM #MyHead;
SET STATISTICS TIME OFF;
GO
PRINT LEFT('--===== FORMAT dd-MMM-YYYY w/o Datatype Match '+REPLICATE('=',100), 100);
SET STATISTICS TIME ON;
DECLARE @Bitbucket CHAR(11);
SELECT @Bitbucket = FORMAT(SomeDate, 'dd-MMM-YYYY')
FROM #MyHead;
SET STATISTICS TIME OFF;
GO
PRINT LEFT('--===== FORMAT dd-MMM-YYYY w/ Datatype Match '+REPLICATE('=',100), 100);
SET STATISTICS TIME ON;
DECLARE @Bitbucket NCHAR(11);
SELECT @Bitbucket = FORMAT(SomeDate,N'dd-MMM-YYYY')
FROM #MyHead;
SET STATISTICS TIME OFF;
GO
Results...
(1000000 row(s) affected)
--===== CONVERT dd-MMM-YYYY ========================================================================
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 264 ms.
--===== FORMAT dd-MMM-YYYY w/o Datatype Match ======================================================
SQL Server Execution Times:
CPU time = 46781 ms, elapsed time = 48565 ms.
--===== FORMAT dd-MMM-YYYY w/ Datatype Match =======================================================
SQL Server Execution Times:
CPU time = 46985 ms, elapsed time = 48280 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2016 at 9:42 am
On that same note, I've repaired the "big", high ROI performance problems associated with the front end for our company and the users still complain of performance problems. The only thing left are a bazillion little things buried in miniscule snippets of code. Just imagine if everyone had made "milliseconds matter" when they wrote all that code so that it was "only" as little as 10 times faster (never mind 180 times faster). You can't buy a machine that will give you that kind of across the board performance increase.
Like I said, "Mind the pennies and the dollars will mind themselves". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2016 at 9:43 am
Jeff, point taken. Thanks for the performance test.
-- Gianluca Sartori
January 25, 2016 at 9:58 am
Luis Cazares (1/25/2016)
the problem often relies on keeping the default (us_english)
I do strongly object to the notion that the default language is English.
I know in almost all science fictions all Aliens speak English, but in reality the world is larger than the English speaking part.
Ben
On a more practical level. I live in Europe and very often software is geared towards the US and does not account for other cultures. We often use different formats for dates, use different constructs for names, use different systems for measurements etc. etc.
With software going more global than ever before, it would be nice if other 'default's would be acknowledged.
January 25, 2016 at 10:05 am
ChrisM@Work (1/25/2016)
How can the code return incorrect results, Ben? Am I missing something here?
The name of the month is language dependend. This often leads to trouble.
When storing a date in a datetime format this is not a problem.
If the name of the month is only used for output, no problem either.
But very often the output of one system is used as the input for another system, and in that case I consider the name of a month as not desirable.
Most of the world consider Januar to be the first month of the year, so using a 1 for a month does not lead to confusion. Using the name 'Gen' for a month might lead to confusion.
Ben
January 25, 2016 at 10:08 am
ben.brugman (1/25/2016)
Luis Cazares (1/25/2016)
the problem often relies on keeping the default (us_english)I do strongly object to the notion that the default language is English.
I know in almost all science fictions all Aliens speak English, but in reality the world is larger than the English speaking part.
Ben
On a more practical level. I live in Europe and very often software is geared towards the US and does not account for other cultures. We often use different formats for dates, use different constructs for names, use different systems for measurements etc. etc.
With software going more global than ever before, it would be nice if other 'default's would be acknowledged.
I get what you're saying. It would be nice if the software could adapt their default values to the country that it's buying it. At least for huge companies such as MS.
I've had the same problem when working in Mexico (I'm Mexican not Italian even if my avatar says otherwise :-D) and most people in charge of installing servers or creating databases just leave the USA defaults.
January 25, 2016 at 10:14 am
ben.brugman (1/25/2016)
ChrisM@Work (1/25/2016)
How can the code return incorrect results, Ben? Am I missing something here?
The name of the month is language dependend. This often leads to trouble.
When storing a date in a datetime format this is not a problem.
If the name of the month is only used for output, no problem either.
But very often the output of one system is used as the input for another system, and in that case I consider the name of a month as not desirable.
Most of the world consider Januar to be the first month of the year, so using a 1 for a month does not lead to confusion. Using the name 'Gen' for a month might lead to confusion.
Ben
That's why we should keep dates as dates until final output. To exchange data from one system to another, if a string representation is absolutely needed (flat files), then ISO-8601 should be used. Definitively not month names.
If someone is expecting Jan and receives Gen or Ene, then there's a bigger problem behind that.
As I mentioned, the problem with the code should only occur when the months don't use 3 characters as short names.
set language french
SELECT UPPER( REPLACE( CONVERT(char(11), GETDATE(), 106), ' ', '-')) , 'Which Year ?'
SELECT UPPER( REPLACE( CONVERT(char(12), GETDATE(), 106), ' ', '-')) , 'This Year'
January 25, 2016 at 10:43 am
Luis Cazares (1/25/2016)
ben.brugman (1/25/2016)
Luis Cazares (1/25/2016)
the problem often relies on keeping the default (us_english)I do strongly object to the notion that the default language is English.
I know in almost all science fictions all Aliens speak English, but in reality the world is larger than the English speaking part.
Ben
On a more practical level. I live in Europe and very often software is geared towards the US and does not account for other cultures. We often use different formats for dates, use different constructs for names, use different systems for measurements etc. etc.
With software going more global than ever before, it would be nice if other 'default's would be acknowledged.
I get what you're saying. It would be nice if the software could adapt their default values to the country that it's buying it. At least for huge companies such as MS.
I've had the same problem when working in Mexico (I'm Mexican not Italian even if my avatar says otherwise :-D) and most people in charge of installing servers or creating databases just leave the USA defaults.
Luis, you know it takes an "expert" to tell the difference between spaghetti and chilli
😎
January 25, 2016 at 11:26 am
Luis Cazares (1/25/2016)
That's why we should keep dates as dates until final output. To exchange data from one system to another, if a string representation is absolutely needed (flat files), then ISO-8601 should be used. Definitively not month names.
+1000 on that. I'll also add that if the output is destined for a GUI or Reporting tool, then the GUI/Reporting Tool should do the formatting so that it can be setup to "automatically" adapt to regional settings.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2016 at 11:55 am
Quick test set, similar results to the one Jeff posted
😎USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_DATES') IS NOT NULL DROP TABLE dbo.TBL_DATES;
CREATE TABLE dbo.TBL_DATES
(
TD_DATE DATETIME NOT NULL PRIMARY KEY CLUSTERED
);
DECLARE @START_DATE DATETIME = CONVERT(DATETIME,'19000101',112);
DECLARE @RCOUNT INT = 1017575;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(@RCOUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
INSERT INTO dbo.TBL_DATES(TD_DATE)
SELECT
DATEADD(HOUR,NM.N,@START_DATE)
FROM NUMS NM;
DECLARE @timer TABLE (T_TEXT VARCHAR(50) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
DECLARE @CHAR_BUCKET VARCHAR(100) = '';
DECLARE @DATETIME_BUCKET DATETIME = 0;
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN');
SELECT
@DATETIME_BUCKET = TD.TD_DATE
FROM dbo.TBL_DATES TD;
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN');
INSERT INTO @timer(T_TEXT) VALUES('FORMAT');
SELECT @CHAR_BUCKET = FORMAT(TD.TD_DATE,N'dd-MMM-YYYY')
FROM dbo.TBL_DATES TD;
INSERT INTO @timer(T_TEXT) VALUES('FORMAT');
INSERT INTO @timer(T_TEXT) VALUES('CONCAT');
SELECT @CHAR_BUCKET = CONCAT(DAY(TD.TD_DATE),CHAR(45),SUBSTRING(UPPER(DATENAME(MONTH,TD.TD_DATE)),1,3),CHAR(45), YEAR(TD.TD_DATE))
FROM dbo.TBL_DATES TD;
INSERT INTO @timer(T_TEXT) VALUES('CONCAT');
INSERT INTO @timer(T_TEXT) VALUES('UPPER REPLACE CONVERT');
SELECT @CHAR_BUCKET = UPPER( REPLACE( CONVERT(char(11), TD.TD_DATE, 106), ' ', '-'))
FROM dbo.TBL_DATES TD;
INSERT INTO @timer(T_TEXT) VALUES('UPPER REPLACE CONVERT');
SELECT
T.T_TEXT
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TEXT
ORDER BY DURATION;
Results on 2nd gen i5, Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
Jun 9 2015 12:06:16
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
T_TEXT DURATION
----------------------- -----------
DRY RUN 134008
CONCAT 756043
UPPER REPLACE CONVERT 2004115
FORMAT 24469399
January 25, 2016 at 12:06 pm
spaghettidba (1/25/2016)
Eirikur Eiriksson (1/25/2016)
spaghettidba (1/25/2016)
Eirikur Eiriksson (1/25/2016)
spaghettidba (1/25/2016)
SQL Server 2012 supports the FORMAT function:
select format(GETDATE(), 'dd-MMM-YYYY')
Gianluca, don't use the format function, it has terrible performance.
😎
It is a CLR function, so there's a startup cost and it performs slower than native functions.
It has the advantage of being simple and easy to understand in your code.
If I'm worrying about the performance of a function I use to format my output, it must be something that really runs like hell on the rest of the execution (reading data from tables) or returns a huge amount of rows. Most of the time it is not so.
Both I and Jeff Moden have posted examples showing the problem with the format function, 40-50 times slower than alternatives in most cases.
😎
No doubt about that.
My point is: does it really matter when I'm formatting 30 or even 100 dates?
Seen this kill a system, single row in the output, formatting a date on a popular website, 10K requests a minute~~~~~~\0/~~~/\~~~~~~
😎
January 25, 2016 at 12:23 pm
Jeff Moden (1/25/2016)
Yes. Not because of the low quantity but because someone else in search of code may find it and use it on something larger or something that's called several 10's of thousands of times per hour. "Take care of the pennies and the dollars will mind themselves" because even the small stuff adds to the overall problems of performance and resource usage.In this particular case, FORMAT is more than 180 times slower. It's really nasty. Little things like this do add up.
We really need to do the article on this we talked about few days back!
😎
January 25, 2016 at 4:50 pm
Eirikur Eiriksson (1/25/2016)
Jeff Moden (1/25/2016)
Yes. Not because of the low quantity but because someone else in search of code may find it and use it on something larger or something that's called several 10's of thousands of times per hour. "Take care of the pennies and the dollars will mind themselves" because even the small stuff adds to the overall problems of performance and resource usage.In this particular case, FORMAT is more than 180 times slower. It's really nasty. Little things like this do add up.
We really need to do the article on this we talked about few days back!
😎
Agreed. I'm working on a presentation coming up for Cleveland SQL Saturday and I thought I'd take a crack at it right after that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply