August 29, 2011 at 11:14 am
Hi all,
Somewhat of an odd question maybe, but it makes sense given my context
I have a table which contains dates that are stored as an MMYY string, for example, "0812" would be "2012-08-01".
I need to find the max date from a collection of records that contain these strings.
So here's the sample data and such:
CREATE TABLE #TestValues
(
ID INT IDENTITY PRIMARY KEY,
SomeValue INT,
FourCharDate CHAR(4)
)
INSERT INTO #TestValues (SomeValue, FourCharDate)
SELECT 1, '0812'
UNION ALL
SELECT 1, '0912'
UNION ALL
SELECT 1, '1012'
UNION ALL
SELECT 1, '0915'
UNION ALL
SELECT 1, '1012'
UNION ALL
SELECT 2, '0711'
UNION ALL
SELECT 2, '0501'
UNION ALL
SELECT 2, '0586'
UNION ALL
SELECT 2, '0512'
UNION ALL
SELECT 3, '0752'
UNION ALL
SELECT 3, '0812'
UNION ALL
SELECT 3, '0812'
SELECT * FROM #TestValues
IDSomeValueFourCharDate
110812
210912
311012
410915
511012
620711
720501
820586
920512
1030752
1130812
1230812
And here's my solution. Just wondering if anyone has a better solution, since mine looks kinda ugly:
SELECT
SomeValue,
RIGHT(MAX(CAST(RIGHT(FourCharDate, 2) AS INT) * 100 + CAST(LEFT(FourCharDate, 2) AS INT)), 2) + RIGHT('0' + LEFT(MAX(CAST(RIGHT(FourCharDate, 2) AS INT) * 100 + CAST(LEFT(FourCharDate, 2) AS INT)), 2), 2) AS FourCharDate
FROM #TestValues
GROUP BY SomeValue
SomeValueFourCharDate
10915
20586
30752
August 29, 2011 at 11:56 am
Since your working with what is essentially just text anyway, just continue treating it as text and do the following:
SELECT MAX(RIGHT(FourCharDate,2) + LEFT(FourCharDate,2)) FROM #TestValues
Result (from your sample data) is 8605, which is correct, (unless that was supposed to be May of 1986?). As long as your data is consistently in the MMYY format, this should work. If you need to add the century, it is only slightly more complicated: determine your cutoff year and prepend either '19' or '20' based on the value of the the rightmost two characters.
Rob Schripsema
Propack, Inc.
August 29, 2011 at 12:01 pm
Hm. So you're using the max of a string value. That works in all cases? Hadn't thought about trying that, 'cause I wasn't sure how SQL Server handles taking the max value from strings.
I guess it would do a character-by-character comparison, using ASCII values? So, ASCII('9') is greater than ASCII('8'), etc..., then compare each character.
How does it do for performance though?
August 29, 2011 at 4:33 pm
kramaswamy (8/29/2011)
Hm. So you're using the max of a string value. That works in all cases? Hadn't thought about trying that, 'cause I wasn't sure how SQL Server handles taking the max value from strings.I guess it would do a character-by-character comparison, using ASCII values? So, ASCII('9') is greater than ASCII('8'), etc..., then compare each character.
How does it do for performance though?
Yes, it will work in all cases -- as long as you can confirm that you'll always have the four character MMYY format in your source data.
I can't vouch for exactly how SQL does a compare on strings, but I've always found speed to be no problem. I'm pretty certain that CASTing the string value to an integer is a more expensive operation than a string compare, in any case.
Rob Schripsema
Propack, Inc.
August 29, 2011 at 5:49 pm
How about this?
; WITH CharsConvertedToDate AS
(
SELECT ID , SomeValue , FourCharDate ,
RN = ROW_NUMBER() OVER ( PARTITION BY SomeValue ORDER BY CAST ( '01-'+STUFF(FourCharDate , 3, 0,'-' ) AS DATETIME) DESC)
FROM #TestValues
)
SELECT SomeValue , FourCharDate
FROM CharsConvertedToDate
WHERE RN = 1
August 30, 2011 at 8:43 am
ColdCoffee (8/29/2011)
How about this?
; WITH CharsConvertedToDate AS(
SELECT ID , SomeValue , FourCharDate ,
RN = ROW_NUMBER() OVER ( PARTITION BY SomeValue ORDER BY CAST ( '01-'+STUFF(FourCharDate , 3, 0,'-' ) AS DATETIME) DESC)
FROM #TestValues
)
SELECT SomeValue , FourCharDate
FROM CharsConvertedToDate
WHERE RN = 1
This depends on the localization settings of the computer. If you're going to go that route, you should use CONVERT rather than CAST so that you can specify the date format. You can also simplify it--since you need to specify the date format anyhow--by using one of the US formats.
CONVERT(datetime, Stuff(FourCharDate, 3, 0, '/01/'), 101)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 30, 2011 at 8:51 am
I'd considered CONVERTing to a date, but I figured that doing a date conversion and then comparison would be more expensive than leaving it as an INT.
Thanks for the input though, all - comparing the INT version I had with the string version posted by Rob yielded roughly the same amount of time for calculation, so I've left it as INT to avoid changing code
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply