handling string date value to dateadd

Last Post 14 Jun 2009 01:49 PM by SwePeso. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
timcronin
New Member
New Member

--
24 Apr 2009 06:51 AM
I have a 3rd party app I need to report off of. It has a field which is populated with verbage such as

1 week
2 days
1 month
1 week 2 days
1 month 5 days

I need to calculate a dateadd value out of this adding to getdate(). I thought case would work but with the "1 week 2 days" value I need a better approach
SwePeso
New Member
New Member

--
14 Jun 2009 01:49 PM
DECLARE @Sample TABLE
(
info VARCHAR(200)
)

INSERT @Sample
SELECT '1 day' UNION ALL
SELECT '2 weeks' UNION ALL
SELECT '3 weeks 4 days' UNION ALL
SELECT '1 month' UNION ALL
SELECT '6 months 7 days' UNION ALL
SELECT '1 month 19 weeks' UNION ALL
SELECT '3 months 2 weeks 1 day'

SELECT GETDATE(),
info,
DATEADD(MONTH, m + 0, DATEADD(DAY, 7 * w + 0, DATEADD(DAY, d + 0, GETDATE()))) AS theDate
FROM (
SELECT REVERSE(info) AS info,
CASE
WHEN m = 0 THEN 0
ELSE SUBSTRING(info, m + 5, LEN(info))
END AS m,
CASE
WHEN w = 0 THEN 0
WHEN m = 0 THEN SUBSTRING(info, w + 4, LEN(info))
ELSE PARSENAME(REPLACE(REVERSE(LTRIM(SUBSTRING(info, w + 4, m - w))), ' ', '.'), 1)
END AS w,
CASE
WHEN d = 0 THEN '0'
ELSE PARSENAME(REVERSE(REPLACE(LTRIM(SUBSTRING(info, d + 3, 9)) + ' ' , ' ', '.')), 1)
END AS d
FROM (
SELECT REVERSE(info) AS info,
PATINDEX('%htnom%', REVERSE(info)) AS m,
PATINDEX('%keew%', REVERSE(info)) AS w,
PATINDEX('%yad%', REVERSE(info)) AS d
FROM @Sample
) AS q
) AS w
SwePeso
New Member
New Member

--
14 Jun 2009 02:06 PM
If values can be only 1 digit, try this

DECLARE @Sample TABLE
(
info VARCHAR(200)
)

INSERT @Sample
SELECT '1 day' UNION ALL
SELECT '2 weeks' UNION ALL
SELECT '3 weeks 4 days' UNION ALL
SELECT '1 month' UNION ALL
SELECT '6 months 7 days' UNION ALL
SELECT '1 month 9 weeks' UNION ALL
SELECT '3 months 2 weeks 1 day'

SELECT GETDATE(),
info,
DATEADD(MONTH, m, DATEADD(DAY, 7 * w + d, GETDATE())) AS theDate
FROM (
SELECT info,
CAST(SUBSTRING(info, PATINDEX('% [0-9] month%', ' ' + info), 1) AS INT) AS m,
CAST(SUBSTRING(info, PATINDEX('% [0-9] week%', ' ' + info), 1) AS INT) AS w,
CAST(SUBSTRING(info, PATINDEX('% [0-9] day%', ' ' + info), 1) AS INT) AS d
FROM @Sample
) AS q


Acceptable Use Policy
---