June 13, 2012 at 7:23 am
Hello, looking for t-sql that would be able to parse the following strings and convert them to just number of days total:
1 Year 6 Months 20 Days
6 Months 4 Days
9 Years 5 Days
June 13, 2012 at 7:34 am
What is the starting point for the conversions, is it today, tomorrow, next week? We need this especially for leap years as the last example may span 2 or 3 leap years depending when you want to calculate the dates from resulting in different outputs.
June 13, 2012 at 7:42 am
are those values you added the actual string? can you provide some example DDL ?
***The first step is always the hardest *******
June 13, 2012 at 7:45 am
I will have a specific date that I will need to to add this calculated # of days to. For example, 20120612. I will be getting this from my database and adding to it the days parsed and calulated to give me a new appointment date. Does that make sense?
Thank you
June 13, 2012 at 8:10 am
I actually don't need the exact date taking into account leap year. I will just assume 1 year is 365 days and 1 month is 30 days. That is close enough for my purposes. IT would be nice to be able to factor those variables in but not totally necessary.
June 13, 2012 at 8:37 am
Okay, if someone can at least show me how to parse the string and put the number for each of the possible date parts into variables, that would be most helpful. for example,
1 Year 6 Months 3 Days
I need to just parse each of the above into
@numYrs = 1
@numMonths = 6
@numDays = 3
From here I can just use the DATEADD function quite easily.
declare
@numYrs int,
@numMos int,
@numWks int,
@complDte datetime,
@apptDte datetime
set @numYrs = 1
set @numMos = 6
set @numWks = 3
set @complDte = '20120613'
SELECT @apptDte = DATEADD(YY ,@numYrs, DATEADD(MM ,@numMos, DATEADD(WK, @numWks, @complDte)))
SELECT @apptDte
Thank you!
June 13, 2012 at 8:42 am
oradbguru (6/13/2012)
Okay, if someone can at least show me how to parse the string ...
No problem...
SELECT
*
FROM ( -- sample data
SELECT '2 Year 6 Months 20 Days' UNION ALL
SELECT '6 Months 4 Days' UNION ALL
SELECT '9 Years 5 Days'
) d (StringDays)
CROSS APPLY (
SELECT
[Years]= CAST(CASE WHEN x.y > 0 THEN LEFT(StringDays,x.y-1) ELSE '0' END AS INT),
[Months]= CAST(CASE WHEN x.m > 0 THEN SUBSTRING(StringDays,x.m-3,2) ELSE '0' END AS INT),
[Days]= CAST(CASE WHEN x.d > 0 THEN SUBSTRING(StringDays,x.d-3,2) ELSE '0' END AS INT)
FROM (
SELECT -- where in the string is the date element?
y = PATINDEX('%Year%',StringDays),
m = PATINDEX('%Month%',StringDays),
d = PATINDEX('%Day%',StringDays)
) x
) Splitter
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 13, 2012 at 9:01 am
Brilliant! This works for me. Thank you!
David
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply