---cross apply example. SQL2K5 +
---This example uses a table valued function to split a date into multiple fragments.
---I primarily use this for adhoc reports in which i'm going to include a pivot table.
--- create table valued function
create function dbo.date_diffuse(@datetime datetime)
returns table as
RETURN ( select
year(@datetime) [dt_year]
, month(@datetime) [dt_month]
, dateName(month,@datetime) [dt_month_name]
, datepart(week, @datetime) [dt_week]
, day(@datetime) [dt_day]
, datepart(hh,@datetime) dt_hour
, convert(varchar(10), @datetime, 101) [dt]
)
go
declare @random_data table (data varchar(128), created datetime)
insert into @random_data (data, created) values ('record 1', '2011-06-14 19:59:06.950')
insert into @random_data (data, created) values ('record 2', '2011-05-02 12:15:10.111')
insert into @random_data (data, created) values ('record 3', '2011-04-03 04:01:01.232')
insert into @random_data (data, created) values ('record 4', '2011-03-21 23:30:00.000')
select *
from @random_data rd
cross apply dbo.date_diffuse(rd.created)