April 24, 2009 at 7:49 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
April 24, 2009 at 7:55 am
Create a UDF.
Inside look for each string 'Week', 'day', 'month' individually , find the number of units per each and add them up individually.
I am off to a meeting so do not have time to code it now. Post back if you can not figure it out or my post is not clear enough.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 24, 2009 at 8:09 am
Is it always in the format number-measure? And are the measures consistent?
For example, would it ever have, "weeks 5, days 3", or will it always be "5 weeks 3 days"? How about "5 wks 3 d"? Any like that?
If it has the kind of consistency that your examples have, something like this might work:
set nocount on;
--
if object_id(N'tempdb..#T') is not null
drop table #T;
--
declare @Interval varchar(100), @Date datetime, @sql varchar(max);
--
select @Interval = '1 year 1 month 1 week 1 day', @Date = '1/1/09';
--
select @Interval = replace(@Interval, 's', ''); -- turns "Days" into "Day", for DateAdd function
--
select row_number() over (order by Number) as row, substring(@Interval + ' ', Number, charindex(' ', @Interval + ' ', Number) - Number) as val
into #T
from dbo.Numbers
where Number <= len(@Interval)
and substring(' ' + @Interval, Number, 1) = ' '
order by Number;
--
select @sql = coalesce(@SQL + ';' + 'select @Date = dateadd(' + t2.val + ', ' + t1.val + ', @Date)',
'select @Date = dateadd(' + t2.val + ', ' + t1.val + ', @Date)')
from #T t1
inner join #T t2
on t1.row = t2.row - 1
and isnumeric(t1.val) = 1
and isnumeric(t2.val) = 0;
select @sql = 'declare @Date datetime; select @Date = ''' + cast(@Date as varchar(100)) + ''';' + @sql + '; select @Date as Date'
print @sql;
exec (@SQL);
Of course, that only does one date at a time, instead of a whole table at once. Might be possible to make it do the whole table, but that's going to be significantly more complex.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2009 at 9:32 am
This function should work for you. The advantage over GSquared's solution is - no dynamic SQL and you can use it inline to get as many records as you want at a time. You may want to work on some error handling but if your data is as clean as the sample you provided you may not need any.
CREATE FUNCTION dbo.fn_getdate(@MessyString VARCHAR(100))
RETURNS DATETIME
AS
BEGIN
DECLARE @MyDate DATETIME
SELECT @MyDate = getdate()
DECLARE @Location INT
DECLARE @Units INT
SET @MessyString = REPLACE(@MessyString, 's', '')
SELECT @Location = CHARINDEX('month', @MessyString)
IF @Location > 0 --found month(s)
BEGIN
SET @Units = CAST(LEFT(@MessyString, @Location - 1) AS INT)
SET @MessyString = SUBSTRING(@MessyString, @Location + 5, LEN(@MessyString) - @Location - 4)
SET @MyDate = DATEADD(month, @Units, @MyDate)
END
SELECT @Location = CHARINDEX('week', @MessyString)
IF @Location > 0 --found weeks(s)
BEGIN
SET @Units = CAST(LEFT(@MessyString, @Location - 1) AS INT)
SET @MessyString = SUBSTRING(@MessyString, @Location + 4, LEN(@MessyString) - @Location - 3)
SET @MyDate = DATEADD(week, @Units, @MyDate)
END
SELECT @Location = CHARINDEX('day', @MessyString)
IF @Location > 0 --found weeks(s)
BEGIN
SET @Units = CAST(LEFT(@MessyString, @Location - 1) AS INT)
SET @MyDate = DATEADD(day, @Units, @MyDate)
END
RETURN @MyDate
END
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply