[dbo].[usm_vw_start

Property	Value
ANSI Nulls On
Quoted Identifier On
Created	11:14:49 AM Tuesday, March 30, 2010
Last Modified	11:14:49 AM Tuesday, March 30, 2010

Name
current_year_start
current_month_start
current_week_start
current_day_start
previous_year_start
previous_month_start
previous_week_start
previous_day_start
current_date_time

Type	Action	Owning Principal
Grant	Select	usmgroup

SQL Script

create view usm_vw_start_datetimes as
select
    convert(datetime,CONVERT(varchar(5),datepart(year,getdate())) + '-' + '1' + '-' + '1' + ' 00:00:00',120) as current_year_start,
    convert(datetime,CONVERT(varchar(5),datepart(year,getdate())) + '-' + CONVERT(varchar(5),datepart(month,getdate())) + '-' + '1' + ' 00:00:00',120) as current_month_start,
    convert(datetime,CONVERT(varchar(5),datepart(year,getdate() - (datepart(weekday,getdate())-1))) + '-' +CONVERT(varchar(5),datepart(month,getdate() - (datepart(weekday,getdate())-1))) + '-' +CONVERT(varchar(5),datepart(day,getdate() - (datepart(weekday,getdate())-1)))+ ' 00:00:00',120) as current_week_start,
    convert(datetime,CONVERT(varchar(5),datepart(year,getdate())) + '-' + CONVERT(varchar(5),datepart(month,getdate())) + '-' + CONVERT(varchar(5),datepart(day,getdate())) + ' 00:00:00',120) as current_day_start,
    convert(datetime,CONVERT(varchar(5),datepart(year,convert(datetime,CONVERT(varchar(5),datepart(year,getdate())) + '-' + '1' + '-' + '1' + ' 00:00:00',120)-1)) + '-' + '1' + '-' + '1' + ' 00:00:00',120) as previous_year_start,
    convert(datetime,CONVERT(varchar(5),datepart(year,convert(datetime,CONVERT(varchar(5),datepart(year,getdate())) + '-' + CONVERT(varchar(5),datepart(month,getdate())) + '-' + '1' + ' 00:00:00',120)-1)) + '-' + CONVERT(varchar(5),datepart(month,convert(datetime,CONVERT(varchar(5),datepart(year,getdate())) + '-' + CONVERT(varchar(5),datepart(month,getdate())) + '-' + '1' + ' 00:00:00',120)-1)) + '-' + '1' + ' 00:00:00',120) as previous_month_start,
    convert(datetime,CONVERT(varchar(5),datepart(year,getdate() - (datepart(weekday,getdate())-1))) + '-' +CONVERT(varchar(5),datepart(month,getdate() - (datepart(weekday,getdate())-1))) + '-' +CONVERT(varchar(5),datepart(day,getdate() - (datepart(weekday,getdate())-1)))+ ' 00:00:00',120) - 7 as previous_week_start,
    convert(datetime,CONVERT(varchar(5),datepart(year,getdate())) + '-' + CONVERT(varchar(5),datepart(month,getdate())) + '-' + CONVERT(varchar(5),datepart(day,getdate())) + ' 00:00:00',120) - 1 as previous_day_start,
    getdate() as current_date_time
GO
GRANT SELECT ON  [dbo].[usm_vw_start_datetimes] TO [usmgroup]
GO