May 2, 2012 at 6:54 pm
Most of us will be in trouble if we go to a country that uses more than 3 character abbreviations for months. 😀
Regardless of language selected (well... at least most of them), this will produce the correct abbreviation for the month and the 2 digit year with that bloody "dash" in there, as well. 😀
SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR(30),GETDATE(),6),4,30),' ','-')
If you don't really care about international possibilities, then stuff it. Seriously. 😛
SELECT STUFF(CONVERT(VARCHAR(30),GETDATE(),7),4,5,'-')
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2012 at 7:08 pm
Jeff Moden (5/2/2012)
Most of us will be in trouble if we go to a country that uses more than 3 character abbreviations for months. 😀Regardless of language selected (well... at least most of them), this will produce the correct abbreviation for the month and the 2 digit year with that bloody "dash" in there, as well. 😀
SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR(30),GETDATE(),6),4,30),' ','-')If you don't really care about international possibilities, then stuff it. Seriously. 😛
SELECT STUFF(CONVERT(VARCHAR(30),GETDATE(),7),4,5,'-')
Jeff, this following produces wrong result for STUFF
SELECT STUFF(CONVERT(VARCHAR(30),'2012-12-12 18:07:33.933',7),4,5,'-')
Result -- 201-12 18:07:33.933
But this produces proper result:
declare @date datetime = '2012-12-12 18:07:33.933'
SELECT STUFF(CONVERT(VARCHAR(30),@date,7),4,5,'-')
I guess, the CONVERT, when supplied with a literal value for date, assumes it to be string, converts to varchar(30) and stuff.
This begs me a question, what does the "style" parameter do then? would it not tell SQL that the value to be converted must be a date and thats why we are specifically passing the "style" value? Hmmm...
May 2, 2012 at 7:40 pm
ColdCoffee (5/2/2012)
Jeff, this following produces wrong result for STUFF
SELECT STUFF(CONVERT(VARCHAR(30),'2012-12-12 18:07:33.933',7),4,5,'-')Result -- 201-12 18:07:33.933
But this produces proper result:
declare @date datetime = '2012-12-12 18:07:33.933'SELECT STUFF(CONVERT(VARCHAR(30),@date,7),4,5,'-')
I guess, the CONVERT, when supplied with a literal value for date, assumes it to be string, converts to varchar(30) and stuff.
This begs me a question, what does the "style" parameter do then? would it not tell SQL that the value to be converted must be a date and thats why we are specifically passing the "style" value? Hmmm...
Seriously??? Would YOU make a "CreatedDate" column a VARCHAR??? Nope. If you do, you might as well use the alphabet instead. 😉 Plus, the original post specified "datetime field".
The Style parameter tells the date what to format to when it's turned into a VARCHAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2012 at 11:19 pm
Jeff Moden (5/2/2012)
ColdCoffee (5/2/2012)
Jeff, this following produces wrong result for STUFF
SELECT STUFF(CONVERT(VARCHAR(30),'2012-12-12 18:07:33.933',7),4,5,'-')Result -- 201-12 18:07:33.933
But this produces proper result:
declare @date datetime = '2012-12-12 18:07:33.933'SELECT STUFF(CONVERT(VARCHAR(30),@date,7),4,5,'-')
I guess, the CONVERT, when supplied with a literal value for date, assumes it to be string, converts to varchar(30) and stuff.
This begs me a question, what does the "style" parameter do then? would it not tell SQL that the value to be converted must be a date and thats why we are specifically passing the "style" value? Hmmm...
Seriously??? Would YOU make a "CreatedDate" column a VARCHAR??? Nope. If you do, you might as well use the alphabet instead. 😉 Plus, the original post specified "datetime field".
The Style parameter tells the date what to format to when it's turned into a VARCHAR.
Jeff, im not debating about CreateDate being a VARCHAR :ermm:
I was surprised at how CONVERT behaves with literal values and local variable. When i harcode a date value into CONVERT function with a value for "style" , the CONVERT treats it as a string and does the STUFF over it.
But when i use a local variable in the same thing, the CONVERT treats it as DATE and does exactly what we need.
And, i would NEVER create a date column with VARCHAR! 🙂
May 3, 2012 at 5:30 am
Worth having a look at Gianluca's article about date formatting here[/url], which covers how this is made much easier in 2012 and also includes a CLR formatter example. If you're doing a lot of these, worth having a look.
This sounds a bit like a BI requirement from the mention of Excel and slicers. It might be worth considering a date dimension which you can fill with useful date formats/FY/CY etc. if that's what you're doing this for...
May 3, 2012 at 6:39 am
ColdCoffee (5/2/2012)
Jeff Moden (5/2/2012)
ColdCoffee (5/2/2012)
Jeff, this following produces wrong result for STUFF
SELECT STUFF(CONVERT(VARCHAR(30),'2012-12-12 18:07:33.933',7),4,5,'-')Result -- 201-12 18:07:33.933
But this produces proper result:
declare @date datetime = '2012-12-12 18:07:33.933'SELECT STUFF(CONVERT(VARCHAR(30),@date,7),4,5,'-')
I guess, the CONVERT, when supplied with a literal value for date, assumes it to be string, converts to varchar(30) and stuff.
This begs me a question, what does the "style" parameter do then? would it not tell SQL that the value to be converted must be a date and thats why we are specifically passing the "style" value? Hmmm...
Seriously??? Would YOU make a "CreatedDate" column a VARCHAR??? Nope. If you do, you might as well use the alphabet instead. 😉 Plus, the original post specified "datetime field".
The Style parameter tells the date what to format to when it's turned into a VARCHAR.
Jeff, im not debating about CreateDate being a VARCHAR :ermm:
I was surprised at how CONVERT behaves with literal values and local variable. When i harcode a date value into CONVERT function with a value for "style" , the CONVERT treats it as a string and does the STUFF over it.
But when i use a local variable in the same thing, the CONVERT treats it as DATE and does exactly what we need.
And, i would NEVER create a date column with VARCHAR! 🙂
Ah... Got it. You're not actually hard coding a "Date Value". You're actually hardcoding a string that looks like a date. If there's nothing to convert it to a date, then it simply behaves like a string. The CONVERT that I'm using is setup to explicitly convert things of the DATETIME datatype to a formatted string. CONVERT isn't like DATEDIFF, DATEPART, etc... it doesn't implicitly do any conversions.
The Style parameter does just one thing. It identifies what style date the VARCHAR is no matter if you're converting DATETIME to VARCHAR or VARCHAR to DATETIME.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2012 at 11:41 am
There's a lot to think about here.
Do we have to convert datetime to varchar (Jeff, ColdCoffee)?
If we don't is it *better not to* (you guys)?
What is internationally sensitive about this one. I don't want to STUFF it, thank you very much (Jeff)?
REPLACE(SUBSTRING(CONVERT(VARCHAR(30),System_CreatedDate,6),4,30),' ','-')
After I went through the following working solutions:
(1) LEFT(CAST(System_CreatedDate as varchar(20)), 3) + '-' + CAST(YEAR(System_CreatedDate) as CHAR(4))
(2) LEFT(DATENAME(mm,System_CreatedDate),3) + '-' + RIGHT(DATENAME(YEAR,System_CreatedDate),2) as MonYr
(3) CONVERT(VARCHAR(20), System_CreatedDate,107) as MonYr
(4) REPLACE(SUBSTRING(CONVERT(VARCHAR(30),System_CreatedDate,6),4,30),' ','-') as MonYr
....I eventually stuck with (2) because it appears it will maintain DATETIME datatype . . .vis a vis DATENAME ...in this way I avoid all conversions to CHAR or VARCHAR.
Next I like (3) best, because it is short.
Next I like the fact that there are so many ways to do this. I'm always afraid that I will not come up with the best solution, but these ALL worked! Quite fine, indeed.
Now there's an article on datetime conversions for Excel that I could read (thanks HowardW), but I'm truly not ready for that. Howard could you elaborate on
This sounds a bit like a BI requirement from the mention of Excel and slicers. It might be worth considering a date dimension which you can fill with useful date formats/FY/CY etc....
?
--Quote me
May 3, 2012 at 12:13 pm
Convert has some associated overhead that will cause your query to be a little slower. How much slower will depend on how large the table is. If you have less than 10,000 rows it is probably not noticeable at all. If however your table contains 1,000,000 rows the time savings in NOT converting can be the reason you end up finding other ways to write your query. For performance sake the datename approach is going to be the best. The code may not be quite as easy to read but the people consuming this query could care less how pretty it is for the developer. More complex code that is well documented should be preferred over pretty code that doesn't perform as well. Hope that answers your question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 3, 2012 at 12:29 pm
What about CAST?
--Quote me
May 3, 2012 at 12:43 pm
Same deal, you are changing the datatype from one to another. Again on a small dataset the performance difference is so little it doesn't make much (if any) difference. It is when you have larger datasets that it would make a noticeable difference.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 3, 2012 at 5:38 pm
polkadot (5/3/2012)
There's a lot to think about here.Do we have to convert datetime to varchar (Jeff, ColdCoffee)?
If we don't is it *better not to* (you guys)?
What is internationally sensitive about this one. I don't want to STUFF it, thank you very much (Jeff)?
REPLACE(SUBSTRING(CONVERT(VARCHAR(30),System_CreatedDate,6),4,30),' ','-')
After I went through the following working solutions:
(1) LEFT(CAST(System_CreatedDate as varchar(20)), 3) + '-' + CAST(YEAR(System_CreatedDate) as CHAR(4))
(2) LEFT(DATENAME(mm,System_CreatedDate),3) + '-' + RIGHT(DATENAME(YEAR,System_CreatedDate),2) as MonYr
(3) CONVERT(VARCHAR(20), System_CreatedDate,107) as MonYr
(4) REPLACE(SUBSTRING(CONVERT(VARCHAR(30),System_CreatedDate,6),4,30),' ','-') as MonYr
....I eventually stuck with (2) because it appears it will maintain DATETIME datatype . . .vis a vis DATENAME ...in this way I avoid all conversions to CHAR or VARCHAR.
Next I like (3) best, because it is short.
Next I like the fact that there are so many ways to do this. I'm always afraid that I will not come up with the best solution, but these ALL worked! Quite fine, indeed.
Now there's an article on datetime conversions for Excel that I could read (thanks HowardW), but I'm truly not ready for that. Howard could you elaborate on
This sounds a bit like a BI requirement from the mention of Excel and slicers. It might be worth considering a date dimension which you can fill with useful date formats/FY/CY etc....
?
The real fact of the matter is that because you don't want the standard return for a DATETIME datatype then, by hook or crook, you need to do at least one conversion, implicitly using DATENAME and the like or explicitly using something like CONVERT to a character based display and, no matter what you do, that's going to take some extra time. Some methods are better than others but it's still going to take extra time. That's why you need to do all the calculations and aggregations you can before you do any conversions for display purposes. That way, you only need to convert a comparatively few display rows instead of possibly millions of rows that need to be aggregated.
What is internationally sensitive about this one. I don't want to STUFF it, thank you very much (Jeff)?
REPLACE(SUBSTRING(CONVERT(VARCHAR(30),System_CreatedDate,6),4,30),' ','-')
Something else you need to get used to is not having all the answers handed out on a silver platter. Change the current language setting so something like German and then try the code to see. Then, go back and look at CONVERT and figure out why it works the way it does. You'll remember a whole lot more by doing so a lot of "us" will throw out a bone and you're supposed to be interested enough to do a little research on your own. 😉
Next I like (3) best, because it is short.
(3) CONVERT(VARCHAR(20), System_CreatedDate,107) as MonYr
Did you actually try it??? The problem is, it doesn't give you what you wanted.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2012 at 8:37 pm
Jeff, I never thought to look up some German date words and try that STUFF line. Thanks for the idea. :pinch:
I did try the CONVERT (as I said) and it did work. I was careful to do everything I said before I posted a claim for each of those itemized statements. Maybe it didn't give me a 'hyphen'. OK
Mostly, thanks for the suggestion that I do all aggregations and calculations first, before date conversions. I suppose I could have found that answer somewhere else too, if I had dug long enough along with the the original reason why I posted. However sometimes, the research is such a whole lot to do, before being able to accomplish ones goal, which is why SQLSERVERCENTRAL is an awesome site. Indeed, every question has many facets and there are a lot of experts here who can answer in the context, in light of the many facets, each of which weighs in slightly differently from problem to problem. This is the unique value that SQLSERVERCENTRAL provides...that customized answer.
I sincerely thank all as always. Not only did I run with the answer and put it to practical use on a ***load of data, but I followed this thread and learned. Really appreciate it.
--Quote me
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply