/*
--select [dbo].[fnc_CRT_RoundDate] (GetDate(),@Year,@Month,@Week,@Day,@Hour,@Minute)
  select [dbo].[fnc_CRT_RoundDate] (GetDate(),NULL ,Null,  2 ,Null ,Null, Null) 
*/

CREATE function [dbo].[fnc_CRT_RoundDate](@Date datetime,
									@Year int,
									@Month int,
									@Week int,
									@Day int,
									@Hour int,
									@Minute int) returns datetime
as
begin
	declare @RoundedDate datetime

	set @RoundedDate = dateadd(millisecond, -datepart(millisecond, @Date) -datepart(second, @Date)*1000, @Date)

	if @Minute is not null
	Begin
		if @Minute > 0
			set @RoundedDate = dateadd(minute, datepart(minute, @RoundedDate) / @Minute * @Minute - datepart(minute, @RoundedDate), @RoundedDate)

	End
	Else Begin
		set @Minute = 60
		set @RoundedDate = dateadd(minute, datepart(minute, @RoundedDate) / @Minute * @Minute - datepart(minute, @RoundedDate), @RoundedDate)	
    End
--select  @Minute, @Date, @RoundedDate

	if @Hour is not null
		Begin
	
			set @RoundedDate = dateadd(hour, datepart(hour, @RoundedDate) / @Hour * @Hour - datepart(hour, @RoundedDate), @RoundedDate)
		End


--select  @Hour, @Date, @RoundedDate

	if @Day is not null
		Begin
			
			If @Day > 1
				set @RoundedDate = 	@RoundedDate - @Day +1
			set @RoundedDate = Cast(Convert(char(10),DateAdd(MINUTE,-DatePart(MINUTE,@RoundedDate), @RoundedDate),121) as datetime )
			--set @RoundedDate = dateadd(day, datepart(day, @RoundedDate) / @Day * @Day - datepart(day, @RoundedDate), @RoundedDate)
		
			IF DatePart(Year,@RoundedDate) < DatePart(Year,@Date)
				Set @RoundedDate = Cast( Cast(DatePart(Year,@Date) as varchar(4))+ '-01-01 00:00:00.000' as datetime)

		End


				



--select  @Day, @Date, @RoundedDate


	if @Week Is Not Null
	Begin
			
			select @RoundedDate = cast(floor(cast(@RoundedDate as float)) as datetime) 
			IF datepart(weekday, @RoundedDate) > 1

				set @RoundedDate = dateadd(day, -datepart(weekday, @RoundedDate)+1 , @RoundedDate)
			
			IF @Week > 1
				set @RoundedDate = dateadd(day, -7*(@Week-1) , @RoundedDate)

			IF DatePart(Year,@RoundedDate) < DatePart(Year,@Date)
				Set @RoundedDate = Cast( Cast(DatePart(Year,@Date) as varchar(4))+ '-01-01 00:00:00.000' as datetime)


			IF DatePart(dw,@RoundedDate) <> 1  
				Set @RoundedDate = DateAdd(day,-DatePart(dw,@RoundedDate)+1,@RoundedDate )

			
	End


		if @Month Is Not Null
		Begin

			Select @RoundedDate = Cast(Convert(char(7),@RoundedDate, 121  ) + '-01 00:00:00.000' as datetime)

			Set @RoundedDate = DateAdd(Month,-(@Month-1),@RoundedDate) 
			IF DatePart(Year,@RoundedDate) < DatePart(Year,@Date)
				Set @RoundedDate = Cast( Cast(DatePart(Year,@Date) as varchar(4))+ '-01-01 00:00:00.000' as datetime)


		End

		if @Year Is Not Null
		Begin
			Set @RoundedDate = Cast( Cast(DatePart(Year,@Date) as varchar(4))+ '-01-01 00:00:00.000' as datetime)
			Set @RoundedDate = DateAdd(Year,-(@Year-1),@RoundedDate) 
		End

	return @RoundedDate
end