January 20, 2011 at 5:53 am
Thanks Jeff for the article. i learned something about the datename function and these langauages settings.
January 20, 2011 at 5:59 am
"You mean an 8% increase in performance with 44% less typing? Don't you wish you could do that everywhere?"
SOLD!
January 20, 2011 at 6:23 am
My experience are just opposite. DATENAME is slower than CONVERT + DATEPART. See below:
/*------------------------
DECLARE @Bitbucket CHAR(4);
PRINT '========== DATENAME Method of Conversion ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = DATENAME(yy,SomeDate)
FROM dbo.JBMTest
SET STATISTICS TIME OFF;
PRINT '========== Original Method of Conversion CONVERT ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = CONVERT(CHAR(4), DATEPART(yy,SomeDate))
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
PRINT '========== Original Method of Conversion CAST ==========';
SET STATISTICS TIME ON;
SELECT @Bitbucket = CAST(DATEPART(yy,SomeDate) AS CHAR(4))
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
------------------------*/
========== DATENAME Method of Conversion ==========
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 718 ms.
========== Original Method of Conversion CONVERT ==========
SQL Server Execution Times:
CPU time = 562 ms, elapsed time = 578 ms.
========== Original Method of Conversion CAST ==========
SQL Server Execution Times:
CPU time = 579 ms, elapsed time = 579 ms.
January 20, 2011 at 6:25 am
This post is slightly off topic. I'm sure I stole this code from somewhere (probably from this site :-)), but I have it in my toolbox of SQL examples. It shows examples all of the CONVERT style codes. I added the abbreviated month + yyyy example from this Spackle post:
DECLARE @date DATETIME
SET@date = GETDATE()
--SET @date = '20090709T175449303'
;WITH cteDates (FormattedDate, Code, Style, SQL) AS
(
SELECT CONVERT(VARCHAR(35), @date, 0) AS FormattedDate,
'0' AS Code,
'Default' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 0)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 1) AS FormattedDate,
'1' AS Code,
'USA date - mm/dd/yy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 1)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 2) AS FormattedDate,
'2' AS Code,
'ANSI date - yy.mm.dd' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 2)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 3) AS FormattedDate,
'3' AS Code,
'UK/French date - dd/mm/yy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 3)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 4) AS FormattedDate,
'4' AS Code,
'German date - dd.mm.yy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 4)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 5) AS FormattedDate,
'5' AS Code,
'Italian date - dd-mm-yy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 5)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 6) AS FormattedDate,
'6' AS Code,
'Abbreviated month - dd mmm yy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 6)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 7) AS FormattedDate,
'7' AS Code,
'Abbreviated month - mmm dd, yy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 7)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 8) AS FormattedDate,
'8 OR 108' AS Code,
'24 hour time - hh:mm:ss' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 8)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 9) AS FormattedDate,
'9 OR 109' AS Code,
'Default with seconds and milliseconds appended mmm dd yyyy hh:mm:ss:mmmXM' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 9)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 10) AS FormattedDate,
'10' AS Code,
'USA date with hyphens - mm-dd-yy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 10)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 11) AS FormattedDate,
'11' AS Code,
'Japanese date - yy/mm/dd' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 11)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 12) AS FormattedDate,
'12' AS Code,
'ISO date - yymmdd' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 12)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 13) AS FormattedDate,
'13 OR 113' AS Code,
'European default with seconds and milliseconds - dd mon yyyy hh:mm:ss:mmm' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 13)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 14) AS FormattedDate,
'14 OR 114' AS Code,
'24 hour time with milliseconds - hh:mm:ss:mmm' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 14)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 20) AS FormattedDate,
'20 OR 120' AS Code,
'ODBC canonical date and time - yyyy-mm-dd hh:mm:ss' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 20)' AS SQL
UNION
SELECT SUBSTRING(CONVERT(VARCHAR(35), @date, 20), 1, 10) AS FormattedDate,
'20' AS Code,
'yyyy-mm-dd' AS Style,
'SELECT SUBSTRING(CONVERT(VARCHAR(35), @date, 20), 1, 10)' AS SQL
UNION
SELECT SUBSTRING(CONVERT(VARCHAR(35), @date, 20), 1, 16)
AS FormattedDate,
'20' AS Code,
'yyyy-mm-dd hh:mm' AS Style,
'SELECT SUBSTRING(CONVERT(VARCHAR(35), @date, 20), 1, 16)'
AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 21) AS FormattedDate,
'21 OR 121' AS Code,
'ODBC canonical date and time with milliseconds - yyyy-mm-dd hh:mm:ss.mmm' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 21)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 101) AS FormattedDate,
'101' AS Code,
'USA date with century - mm/dd/yyyy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 101)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 102) AS FormattedDate,
'102' AS Code,
'ANSI date with century - yyyy.mm.dd' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 102)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 103) AS FormattedDate,
'103' AS Code,
'UK / French date with century - dd/mm/yyyy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 103)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 104) AS FormattedDate,
'104' AS Code,
'German date with century - dd.mm.yyyy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 104)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 105) AS FormattedDate,
'105' AS Code,
'Italian date with century - dd-mm-yyyy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 105)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 106) AS FormattedDate,
'106' AS Code,
'Abbreviated month with century - dd mmm yyyy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 106)' AS SQL
UNION
SELECT RIGHT(CONVERT(CHAR(11),@date, 106), 8) AS FormattedDate,
'106' AS Code,
'Abbreviated month - mmm yyyy' AS Style,
'SELECT RIGHT(CONVERT(CHAR(11), @date, 106), 8)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 107) AS FormattedDate,
'107' AS Code,
'Abbreviated month with century - mmm dd, yyyy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 107)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 110) AS FormattedDate,
'110' AS Code,
'USA date with hyphens and century - mm-dd-yyyy' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 110)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 111) AS FormattedDate,
'111' AS Code,
'Japanese date with century - yyyy/mm/dd' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 111)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 112) AS FormattedDate,
'112' AS Code,
'ISO date with century - yyyymmdd' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 112)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 112) + ' ' + REPLACE(CONVERT(VARCHAR(35), @date, 108), ':', '')
AS FormattedDate,
'112 + 108' AS Code,
'yyyymmdd hhmmss' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 112) + '' '' + REPLACE(CONVERT(VARCHAR(35), @date, 108), '':'', '''')'
AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 112) + ' ' + REPLACE(CONVERT(VARCHAR(35), @date, 114), ':', '')
AS FormattedDate,
'112 + 114' AS Code,
'yyyymmdd hhmmssfff' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 112) + '' '' + REPLACE(CONVERT(VARCHAR(35), @date, 114), '':'', '''')'
AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 112) + ' ' + CONVERT(VARCHAR(35), @date, 114) AS FormattedDate,
'112 + 114' AS Code,
'yyyymmdd hh:mm:ss:fff' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 112) + '' '' + CONVERT(VARCHAR(35), @date, 114)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 126) AS FormattedDate,
'126' AS Code,
'ISO8601 - for use in XML - yyyy-mm-ddThh:mm:ss:mmm' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 126)' AS SQL
UNION
SELECT CONVERT(VARCHAR(35), @date, 127) AS FormattedDate,
'127' AS Code,
'ISO8601 with timezone Z - yyyy-mm-ddThh:mm:ss:mmmZ' AS Style,
'SELECT CONVERT(VARCHAR(35), @date, 127)' AS SQL
)
--
--SELECT * FROM cteDates AS cted ORDER BY cted.Code
SELECT SortKey =
CASE
WHEN CHARINDEX(' ', cted.Code) = 0
THEN cted.Code
ELSE
CAST(SUBSTRING(cted.Code, 1, CHARINDEX(' ', cted.Code)) AS INT)
END,
cted.FormattedDate, cted.Code, cted.Style, cted.SQL
FROM cteDates AS cted
ORDER BY SortKey, cted.FormattedDate
January 20, 2011 at 6:28 am
Thanks to David for pointing out the bug in my code. I've gotten so used to the SET LANGUAGE syntax without the string delimiters! I've fixed the code in my original posting just in case anyone else uses it.
Best wishes,
Phil Factor
January 20, 2011 at 6:53 am
@clark_button - (going slightly off topic too) You could also try this, that Robyn and I wrote a while back for Simple-Talk
[font="Courier New"]DECLARE @types TABLE(
[2 digit year] INT NULL,
[4 digit year] INT NOT NULL,
name VARCHAR(40))
SET LANGUAGE british SET NOCOUNT ON
--Each select statement is followed by an example output string using the style
INSERT INTO @types
VALUES
(NULL,100,'Default'),--Oct 17 2006 9:29PM
(1,101, 'USA'), --10/17/06 or 10/17/2006
(2,102, 'ANSI'), --06.10.17 or 2006.10.17
(3,103, 'British/French'),--17/10/06 or 17/10/2006
(4,104, 'German'), --17.10.06 or 17.10.2006
(5,105, 'Italian'), --17-10-06 or 17-10-2006
(6,106, 'dd mon yy'),--17 Oct 06 or 17 Oct 2006
(7,107, 'Mon dd, yy'),--Oct 17, 06 or Oct 17, 2006
(8,108, 'hh:mm:ss'), --21:29:45 or 21:29:45
(NULL,109, 'Default + milliseconds'),--Oct 17 2006 9:29:45:500PM
(10,110,'USA'), --10-17-06 or 10-17-2006
(11,111,'JAPAN'), --06/10/17 or 2006/10/17
(12,112,'ISO'), --061017 or 20061017
(NULL,113,'Europe default(24h) + milliseconds'),--17 Oct 2006 21:29:45:500
(14,114,'hh:mi:ss:mmm (24h)'), --21:29:45:500 or 21:29:45:500
(NULL,120,'ODBC canonical (24h)'),--2006-10-17 21:29:45
(NULL,121, 'ODBC canonical (24h)+ milliseconds'),--2006-10-17 21:29:45.500
(NULL,126, 'ISO8601'),--2006-10-17T21:29:45.500
(NULL,127, 'ISO8601 with time zone'), --SQL Server 2005 only!
(NULL,130, 'Hijri'), --25 ????? 1427 9:33:21:340PM
(NULL,131, 'Hijri') --25/09/1427 9:29:45:500PM
SELECT [name],
[2 digit year]=COALESCE(CONVERT(NVARCHAR(3),[2 digit year]),'-'),
[example]=CASE WHEN [2 digit year] IS NOT NULL
THEN CONVERT(NVARCHAR(30),GETDATE(),[2 digit year])
ELSE '-' END,
[4 digit year]=COALESCE(CONVERT(NVARCHAR(3),[4 digit year]),'-'),
[example]=CASE WHEN [4 digit year] IS NOT NULL
THEN CONVERT(NVARCHAR(30),GETDATE(),[4 digit year])
ELSE '-' END
FROM @types
[/font]
Best wishes,
Phil Factor
January 20, 2011 at 7:12 am
Well written AND entertaining. Nice. 🙂
I've been using SqlSvr since the 6.5 days I still learn new stuff everyday. I have to admit I usually use DatePart - mostly because I had forgotten that DateName existed.
So thanks for the reminder, Jeff. Hope you'll write some more articles in the future.
January 20, 2011 at 7:34 am
I use a "throw away" variable (@BitBucket)
BWAA-HAA!!!
Hmmmm
The quotes aside .. Jeff a great article with more sample code than most of us deserve.
Thanks, as I put this into my personal "toolbox"
January 20, 2011 at 7:43 am
Thanks for the tips on DATENAME (a function I think I'd used awhile back but had faded from my memory)
I too would have done something like (limiting the convert output to 3 chars makes you not need to do Left/Substring):
SELECT CONVERT(varchar(3),MyDateField,109)
But the DATENAME method makes for more readable code. Luckily I don't have to deal with any language but English in my current job, but things like this are good to file away in case that changes down the road.
January 20, 2011 at 7:43 am
David McKinney (1/20/2011)
I did a little more hunting and I came across sys.syslanguages which looks quite promising.To continue with the French exception, it has fields months and shortmonths with the comma separated values you see below.
janvier,février,mars,avril,mai,juin,juillet,août,septembre,octobre,novembre,décembre
janv,févr,mars,avr,mai,juin,juil,août,sept,oct,nov,déc
This merits consideration for a localised solution.
That would seem to indicate that the CONVERT method may work correctly. I'll have to give it a try. Thanks again, David.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2011 at 7:56 am
marcin.motyl (1/20/2011)
Different methodDECLARE @Bitbucket CHAR(10);
SET STATISTICS TIME ON;
SELECT @Bitbucket = substring(convert(varchar(100), SomeDate,13) , 4,8)
FROM dbo.JBMTest
SET STATISTICS TIME OFF;
and results:
CPU time = 1078 ms, elapsed time = 1073
Tiempo de CPU = 1047 ms, tiempo transcurrido = 1042 ms.
CPU-Zeit = 1062 ms, verstrichene Zeit = 1056 ms.
I only see three outputs there and the were 3 in the test code I provided. I also don't see the "markers" to identify which is which so it looks like you ran your code 3 different times with 3 different language settings.
Because of the differences between machines and the fact that I did my testing on a 9 year single CPU machine, you need to add your code (hopefully with markers) to the test bed I provided so we have something to compare your method against.
Thanks for the feedback and the code example. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2011 at 8:02 am
surreydude. (1/20/2011)
Interesting article. Mal-formed dates are the bane of my professional life; often this goes hand-in-hand with Excel data and/or lack of user discipline and validation.I've experienced date-related issues whilst working around the globe and in my experience, the US has the worst practices, whilst continental Europe has better disciplined users and validation. Whenever I am involved in the design/ETL phase of a project concerning data with dates, I go that bit extra to ensure that we have a least understood the magnitude of the problems around storage of such data. I favour always referencing dates in full ISO format, but it is important that people understand the format being used, rather than assume based on their locale. Where ambiguity can sneak in, I will use a format that is explicit and localised.
One thing I did notice about the article, however, was the use of yy for year formats. It would seem that the "Y2K Bug" has not taught us very much afterall!
I absolutely agree which is why I put the disclaimer/warning near the beginning of the article. Any time dates need to be transmitted as characters (ugh!) or integers (ugh!) in a "readable" file, I'll try to compell folks to use the ISO format in this global environment.
So far as the "yy" thing goes... it's just a format selector like "106" is. I don't like to use words that have multiple code meanings like YEAR, MONTH, or DAY. Sometimes it can't be avoided but I try.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2011 at 8:04 am
nigel. (1/20/2011)
David McKinney (1/20/2011)
Très bien, Jeff! Except of course that you can't keep everybody happy!In France, June and July are Juin and Juillet respectively, and hence JUI and JUI when truncated to 3 characters.
It gets even worse for languages such as Finnish, Estonian, & Czech (to name but a few).
For instance Czech uses the Roman numerals (I,II,III,IV etc.) for short month names, and in Estonian the short month names vary in length from 3 to 5 characters.
I haven't tried it because all of my work has been in English speaking countries but I believe the CONVERT method in the article may handle that with a slight modification using SUBSTRING(____, 3,20) instead of RIGHT(____,8)
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2011 at 8:08 am
is this a SQL 2008 only function or is it in SQL 2005 as well?
January 20, 2011 at 8:10 am
eric.hinrichsen (1/20/2011)
Thanks for the tips on DATENAME (a function I think I'd used awhile back but had faded from my memory)I too would have done something like (limiting the convert output to 3 chars makes you not need to do Left/Substring):
SELECT CONVERT(varchar(3),MyDateField,109)But the DATENAME method makes for more readable code. Luckily I don't have to deal with any language but English in my current job, but things like this are good to file away in case that changes down the road.
As you can see from the previous posts, the DATENAME method and my CONVERT method doesn't quite cut it internationally. You'd need to use SUBSTRING(_____,3,20) on the convert method to get the short month name and year to come out in France (for example).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 108 total)
You must be logged in to reply to this topic. Login to reply