March 1, 2013 at 1:38 am
Hi,
I use
DATEPART(month, myDate)
or
MONTH(myDate)
the resut is: 2 (if myDate is 2013.02.03). I would like return: 02
Do you have any solution for this?
Thans!
March 1, 2013 at 2:17 am
do you want this
select RIGHT('0'+CONVERT(VARCHAR(2),month(GETDATE())),2)
March 1, 2013 at 2:43 am
Thank you very much!
March 1, 2013 at 1:09 pm
Or simply:
SELECT CONVERT(char(2), myDate, 1)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 1, 2013 at 1:57 pm
ScottPletcher (3/1/2013)
Or simply:
SELECT CONVERT(char(2), myDate, 1)
I don't think will quite work Scott. The OP said they are using month(MyDate) which will return an int. Casting a single digit int to a char(2) will result in 'x '.
declare @MyDate datetime = '20130301'
select convert(char(2), month(@MyDate), 1)
_______________________________________________________________
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/
March 1, 2013 at 2:15 pm
Sean Lange (3/1/2013)
ScottPletcher (3/1/2013)
Or simply:
SELECT CONVERT(char(2), myDate, 1)
I don't think will quite work Scott. The OP said they are using month(MyDate) which will return an int. Casting a single digit int to a char(2) will result in 'x '.
declare @MyDate datetime = '20130301'
select convert(char(2), month(@MyDate), 1)
Since myDate is a datetime, you can use a straight CONVERT, no MONTH() at all. My point was, given that, MONTH() was wasted effort that forced even further wasted effort: once you use MONTH(), then you have to use RIGHT() and concatenation.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 1, 2013 at 2:51 pm
ScottPletcher (3/1/2013)
Sean Lange (3/1/2013)
ScottPletcher (3/1/2013)
Or simply:
SELECT CONVERT(char(2), myDate, 1)
I don't think will quite work Scott. The OP said they are using month(MyDate) which will return an int. Casting a single digit int to a char(2) will result in 'x '.
declare @MyDate datetime = '20130301'
select convert(char(2), month(@MyDate), 1)
Since myDate is a datetime, you can use a straight CONVERT, no MONTH() at all. My point was, given that, MONTH() was wasted effort that forced even further wasted effort: once you use MONTH(), then you have to use RIGHT() and concatenation.
Oh sure, make it more simple, faster and easier to understand. Somehow I missed you not using MONTH. Sometimes I seriously need a "post checker" before I hit that button. 😉
_______________________________________________________________
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/
March 1, 2013 at 9:25 pm
ScottPletcher (3/1/2013)
Or simply:
SELECT CONVERT(char(2), myDate, 1)
I'm not poking holes here so go easy. Considering another conversation about not so obvious code, wouldn't the concatenation method of adding a zero be more correct insofar as the easy readability you were speaking of?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2013 at 12:08 pm
Jeff Moden (3/1/2013)
ScottPletcher (3/1/2013)
Or simply:
SELECT CONVERT(char(2), myDate, 1)
I'm not poking holes here so go easy. Considering another conversation about not so obvious code, wouldn't the concatenation method of adding a zero be more correct insofar as the easy readability you were speaking of?
This also relies on silent truncation of the full string. Something I have seen people complain about in T-SQL.
March 3, 2013 at 4:48 pm
In this case, the silent truncation is actually a desirable thing. I just think it odd that Scott would favor this particular method because someone not-in-the-know would have to make a trip to BOL to figure out what date format #1 was whereas the other method is absolutely clear. Scott had opposed a method I used for something similar because it required a bit of extra knowledge of the developer just like this code of his does.
That, notwithstanding, I'd use Scott's method instead of the other method simply because it's almost twice as fast. A simple comment could be included in Scott's code if someone felt an explanation was necessary.
Performance Proof:
DECLARE @Bitbucket CHAR(2);
SET STATISTICS TIME ON;
SELECT TOP 2000000
@Bitbucket = CONVERT(CHAR(2), GETDATE(), 1)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
SELECT TOP 2000000
@Bitbucket = RIGHT('0'+CONVERT(VARCHAR(2),MONTH(GETDATE())),2)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
SET STATISTICS TIME OFF;
Results on my 11 year old desktop.
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 706 ms.
SQL Server Execution Times:
CPU time = 954 ms, elapsed time = 971 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2013 at 4:54 pm
This method requires that the developer understand the date format code and that silent truncation is taking place. Like I said, how many times have we seen people complain about it occuring thinking that it should throw an error.
March 3, 2013 at 5:06 pm
Lynn Pettis (3/3/2013)
This method requires that the developer understand the date format code and that silent truncation is taking place. Like I said, how many times have we seen people complain about it occuring thinking that it should throw an error.
While I agree, that's why comments were invented. I'm one of those folks that will go for the highest performance possible (2nd only to accuracy) even if it does require a bit of the arcane. How many people have you and I taught the Tally Table to or how to find the first day of a month? Tell me that neither of those are arcane knowledge. Neither usage even shows up in BOL. Both require arcane knowledge easily made up for by a simple, well placed, well written comment.
And, that's the point I was trying to make with Scott on another thread. We do all sorts of stuff that requires not-so-obvious knowledge, so go for the speed and write a comment. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2013 at 5:16 pm
P.S. Let's hope the Microsoft never fixes the "silent truncation" problem. I rely on it a whole lot! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2013 at 5:51 pm
Jeff Moden (3/3/2013)
Lynn Pettis (3/3/2013)
This method requires that the developer understand the date format code and that silent truncation is taking place. Like I said, how many times have we seen people complain about it occuring thinking that it should throw an error.While I agree, that's why comments were invented. I'm one of those folks that will go for the highest performance possible (2nd only to accuracy) even if it does require a bit of the arcane. How many people have you and I taught the Tally Table to or how to find the first day of a month? Tell me that neither of those are arcane knowledge. Neither usage even shows up in BOL. Both require arcane knowledge easily made up for by a simple, well placed, well written comment.
And, that's the point I was trying to make with Scott on another thread. We do all sorts of stuff that requires not-so-obvious knowledge, so go for the speed and write a comment. 🙂
We are in full agreement! I have learned the same from you. God enough just isn't good enough anymore. It has to scale, and beyond what one may think occur.
March 4, 2013 at 8:19 am
Jeff Moden (3/3/2013)
In this case, the silent truncation is actually a desirable thing. I just think it odd that Scott would favor this particular method because someone not-in-the-know would have to make a trip to BOL to figure out what date format #1 was whereas the other method is absolutely clear. Scott had opposed a method I used for something similar because it required a bit of extra knowledge of the developer just like this code of his does.That, notwithstanding, I'd use Scott's method instead of the other method simply because it's almost twice as fast. A simple comment could be included in Scott's code if someone felt an explanation was necessary.
Performance Proof:
DECLARE @Bitbucket CHAR(2);
SET STATISTICS TIME ON;
SELECT TOP 2000000
@Bitbucket = CONVERT(CHAR(2), GETDATE(), 1)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
SELECT TOP 2000000
@Bitbucket = RIGHT('0'+CONVERT(VARCHAR(2),MONTH(GETDATE())),2)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
SET STATISTICS TIME OFF;
Results on my 11 year old desktop.
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 706 ms.
SQL Server Execution Times:
CPU time = 954 ms, elapsed time = 971 ms.
I think that's the cleanest and fastest code to do this task, since a format code is needed either way.
But yes, I've repeatedly stated I'm against using format codes for literal date values. I've also given the reason: 'YYYYMMDD[ hh:mm:ss.sss]' format always works, regardless of date and/or language settings.
Given that, I think introducing a format code adds unnecessary chances for errors. Say I copy code with a literal date of '07/04/2012', format code 103, from a non-English source to use in my English code. I think that adds work: the person reading that code later must: (1) look the code up; (2) not bother to look it up and perhaps misinterpret the value, because they're used to mm/dd/yyyy; (3) much less likely, but more ominously, accidentally or not, change the code to the more-familiar 101.
No format code, otoh, no such issues are even possible. Thus, I still strongly recommend that format codes not be used for literal dates. I'm not so sure why you insist on using codes for literal dates, but I prefer not to.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply