SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Formatting Dates with 3 Character Months (SQL Spackle)


Formatting Dates with 3 Character Months (SQL Spackle)

Author
Message
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59859 Visits: 13297
katesl (1/20/2011)
Question from a DBA--

I have to ask this question because I have no experience doing user interface programming-- I use T-SQL and SSMS, and Excel to deliver reports, for everything I do. In the article, the T-SQL is presented in a window with horizontal scroll bars. What is the name by which to refer to such a window? It does not allow copy. Is this a problem of the particular browser I'm using or is it by design? With less elaborate user interface, I was able to copy the code from the article and paste it into the SSMS query window. Why is this scroll bar window supposed to be better than what worked just fine?

Thank you.


This is quite bizarre.
The first time I couldn't copy paste either from the article to test things out.
But when I opened the article for a second time, I could copy paste everything.

Weird IE problem?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
nigel.
nigel.
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3153 Visits: 2907
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.

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Phil Factor
Phil Factor
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4720 Visits: 3031
Yes, the correct shortnames for the months are listed in sys.syslanguages

If you run this code, it should show you how, in SQL Server, all the languages format the current date using the date format code 113. You'll see that the shortmonthname is correctly inserted into the date if you use CONVERT. Is there a way of using DateName to get the correct short-date? I haven't discovered it!

DECLARE @TestEmOut NVARCHAR(MAX)
CREATE TABLE #ShortMonthNames (NAME NVARCHAR(20), Alias NVARCHAR(30), Date NVARCHAR(30), ShortMonthNames NVARCHAR(255))
SELECT @TestEmOut=''
SELECT @TestEmOut= @TestemOut+'
Set language N'''
+name+'''
insert into #shortMonthNames (Name, Alias, Date, ShortMonthNames)
SELECT N'''
+name+''',N'''+alias+''', CONVERT(nVARCHAR(80),GetDate(),113),
(Select shortmonths from sys.syslanguages where name like N'''
+NAME+''')'
FROM sys.syslanguages
EXEC(@TestEmOut)
SELECT * FROM #ShortMonthNames
DROP TABLE #ShortMonthNames


(Edited after David McKinney's help with setting the language to Brasilian portugese. Oops!)


Best wishes,

Phil Factor
Simple Talk
WayneS
WayneS
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20724 Visits: 10652
surreydude. (1/20/2011)
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!


The only place where "yy" is being used in the article is in the DateName(yy, ) parts. In this, the "yy" (or "yyyy") is just the abbreviation for "year" - using any of these returns the full year (all four characters). See the DateName BOL - specifically look at the datepart chart in the Arguments section. And in the Return Value section is this:
Each datepart and its abbreviations return the same value.


So, would this be myth... busted?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

David McKinney
David McKinney
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2663 Visits: 2090
WHERE name NOT IN ('Português (Brasil)'


Thanks Phil.

Putting the language in quotes will solve the Portuguese Brazil problem

Set language ['+name+']....
sharath.chalamgari
sharath.chalamgari
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1950 Visits: 798
Thanks Jeff for the article. i learned something about the datename function and these langauages settings.
Don Halloran
Don Halloran
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1251 Visits: 1490
"You mean an 8% increase in performance with 44% less typing? Don't you wish you could do that everywhere?"

SOLD!

Blog on sqlservercentral
radyo
radyo
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 276
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.


clark_button
clark_button
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 396
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


Phil Factor
Phil Factor
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4720 Visits: 3031
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
Simple Talk
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search