April 25, 2006 at 12:35 pm
I would like to have a function (or some programming component) that can take a column of dates and return two date columns amongst the rest of the returned columns.
For example the 'function' would look something like this:
CREATE FUNCTION GetMonth (@EntryDate smalldatetime)
AS BEGIN
DECLARE @mySDT smalldatetime
SELECT @mySDT = CAST(CAST(YEAR(@EntryDate) as varchar(4)) + '-' + CAST(MONTH(@EntryDate) As varchar(2)) + '-01' AS smalldatetime)
SELECT @mySDT, DateAdd(minute, -1, DateAdd(month, 1, @mySDT))
END
The function takes a date and returns two SmallDateTimes, one with at the very first minute of the same month as the entry date and one at the very last minute. The results of this function will be unioned with another result set.
I would like to do this with a single construct since the 2nd date is derivative of the first. I can do this with two seperate scalar UDFs but I'm not sure the results of the first UDF would be cached.
For instance, given a query similar to the following:
SELECT ID, GetMonthStart(DateField) AS MonthStart, DateAdd(minute, -1, GetMonthStart(DateField)) AS MonthEnd
FROM SomeTable
I would be happy if I knew that the results of GetMonthStart(DateField) for MonthStart were cached an plugged into the DateAdd functions...
Any advice would be welcomed.
Thank you,
Steamer25
April 26, 2006 at 5:32 am
Hi Brett,
I would either simply create the 2 separate UDFs and use those (see here: http://www.sqlteam.com/forums/forum.asp?FORUM_ID=11 for more date UDFs than you know what to do with).
Or, if the above isn't fast enough, create some kind of static months/dates table and join to that (again see the link above for more about 'date' tables). Something along these lines (to give you an idea)...
--preparation (in reality make @months a static, indexed table - i.e. months, not @months)
declare @numbers table (i int identity(0, 1), x bit)
insert @numbers select top 2000 null from master.dbo.syscolumns a, master.dbo.syscolumns b
declare @months table (startDate datetime, endDate datetime, firstMinute datetime, lastMinute datetime)
insert @months
select
dateadd(month, i, 0),
dateadd(month, i+1, 0),
dateadd(minute, 1, dateadd(month, i, 0)),
dateadd(minute, -1, dateadd(month, i+1, 0))
from @numbers
--data
declare @t table (id int identity(1, 1), d datetime)
insert @t
select getdate() + i + rand(1000*i) from @numbers
--calculation
select * from @t t inner join @months m on t.d >= m.startDate and t.d < m.endDate
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply