/****** Object:  User Defined Function dbo.date_trunc    Script Date: 12/1/2005 11:50:35 PM ******/

CREATE FUNCTION [dbo].[date_trunc](@component varchar(10) ,@fDate datetime)  
RETURNS datetime AS  
BEGIN
--DECLARE @temp varchar(120)
IF @component IN('year','years', 'yr', 'yrs')
    RETURN CONVERT(datetime, CONVERT(varchar(35), DATEPART(year, @fDate)))
IF @component IN('month', 'months', 'mo', 'mos')
    RETURN CONVERT(datetime,  DATENAME(month, @fDate) + ' ' + DATENAME(year, @fDate))    
IF @component IN('day', 'days')
    RETURN CONVERT(datetime, CONVERT(varchar(35), @fDate, 10))
IF @component IN('hour', 'hours', 'hr', 'hrs')
    RETURN DATEADD(hour, DATEPART(hour, @fDate), CONVERT(datetime, CONVERT(varchar(35), @fDate, 10)))
IF @component IN('minute', 'minutes', 'min', 'mins')
    RETURN DATEADD(second, 0 - DATEPART(second, @fDate), CONVERT(datetime, CONVERT(varchar(35), @fDate, 120)))
IF @component IN('second', 'seconds', 'sec', 'secs')
    RETURN CONVERT(datetime, CONVERT(varchar(35), @fDate, 120))
IF @component IN('week', 'weeks', 'wk', 'wks')
    RETURN CONVERT(datetime, CONVERT(varchar(20), DATEADD(day, 1 - DATEPART(dw, @fDate), @fDate), 1))
IF @component IN('iso-week', 'iso-wk')
    BEGIN
        DECLARE @num_days int, @firstDay datetime
        SET @firstDay = dbo.isoyear(@fDate)
        SET @num_days = DATEDIFF(day, @firstDay, @fDate)
        RETURN DATEADD(week, FLOOR(@num_days/7), @firstDay)
    END
IF @component IN('quarter', 'quarters', 'qtr', 'qtrs')
    RETURN DATEADD(quarter, DATEPART(quarter, @fDate) -1 , CONVERT(datetime, CONVERT(varchar(35), DATEPART(year, @fDate))))
RETURN  @fDate
END
GO