Some Useful Function For DateTime

Kindly note these functions for DateTime

SELECT dbo.get_DateWithStartTime(getdate()) AS DateWithStartTime

--Return getdate with start time

--e.g 2013-02-06 00:00:00.000

SELECT dbo.get_DateWithEndTime(getdate()) AS DateWithEndTime

--Return getdate with End time e.g

--2013-02-06 23:59:59.000

SELECT dbo.get_StartDate(getdate()) AS StartDateOfMonth

--Return getdate with start time and start Date

--e.g 02/01/2013 00:00:00

SELECT dbo.get_EndDate(getdate()) AS EndDateOfMonth

--Return getdate with start time and end Date

--e.g 02/28/2013 23:59:59

CREATE FUNCTION dbo.[get_DateWithStartTime](@Date DATETIME)     

RETURNS DATETIME     

AS     

BEGIN     

      

 DECLARE @newDate DATETIME 

 SET @newDate = @Date 

 

 Set @newDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @newDate)) 

 Set @newDate = DATEADD(Hour, 00, @newDate) 

 Set @newDate = DATEADD(Minute, 00, @newDate) 

 Set @newDate = DATEADD(Second, 00, @newDate) 

     

 RETURN @newDate     

END

CREATE FUNCTION dbo.[get_DateWithEndTime](@Date DATETIME)     

RETURNS DATETIME     

AS     

BEGIN     

      

 DECLARE @newDate DATETIME 

 SET @newDate = @Date 

 

 Set @newDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @newDate)) 

 Set @newDate = DATEADD(Hour, 23, @newDate) 

 Set @newDate = DATEADD(Minute, 59, @newDate) 

 Set @newDate = DATEADD(Second, 59, @newDate) 

     

 RETURN @newDate     

END

CREATE FUNCTION dbo.[get_StartDate](@Date DateTime)     

RETURNS VARCHAR(100)     

AS     

BEGIN     

      

 DECLARE @MyDate nvarchar(max);     

 DECLARE @StartDate nvarchar(max);   

 

 DECLARE @MyYear nvarchar(max);     

 DECLARE @MyMonth nvarchar(max);    

 

 SET @MyYear = Year(@Date); 

 SET @MyMonth = MONTH(@Date);  

       

 select @MyDate =      

    CASE @MyMonth       

     WHEN '1' THEN '1/1/' + @MyYear     

     WHEN '2' THEN '2/1/' + @MyYear     

     WHEN '3' THEN '3/1/' + @MyYear     

     WHEN '4' THEN '4/1/' + @MyYear     

     WHEN '5' THEN '5/1/' + @MyYear     

     WHEN '6' THEN '6/1/' + @MyYear     

     WHEN '7' THEN '7/1/' + @MyYear     

     WHEN '8' THEN '8/1/' + @MyYear     

     WHEN '9' THEN '9/1/' + @MyYear     

     WHEN '10' THEN '10/1/' + @MyYear     

     WHEN '11' THEN '11/1/' + @MyYear     

     WHEN '12' THEN '12/1/' + @MyYear     

    End     

     

 SELECT @StartDate = CONVERT(VARCHAR(25)     

      ,DATEADD(dd,-(DAY(@MyDate)-1),@MyDate)     

      ,101)      

     

 SET @StartDate = @StartDate + ' 00:00:00'      

     

 RETURN @StartDate     

END

CREATE FUNCTION dbo.[get_EndDate](@Date DateTime)  

RETURNS VARCHAR(100)    

AS   

BEGIN   

        

 DECLARE @MyDate nvarchar(max);     

 DECLARE @StartEnd nvarchar(max);   

 

 DECLARE @MyYear nvarchar(max);     

 DECLARE @MyMonth nvarchar(max);    

 

 SET @MyYear = Year(@Date); 

 SET @MyMonth = MONTH(@Date);  

       

 select @MyDate =      

    CASE @MyMonth       

     WHEN '1' THEN '1/1/' + @MyYear     

     WHEN '2' THEN '2/1/' + @MyYear     

     WHEN '3' THEN '3/1/' + @MyYear     

     WHEN '4' THEN '4/1/' + @MyYear     

     WHEN '5' THEN '5/1/' + @MyYear     

     WHEN '6' THEN '6/1/' + @MyYear     

     WHEN '7' THEN '7/1/' + @MyYear     

     WHEN '8' THEN '8/1/' + @MyYear     

     WHEN '9' THEN '9/1/' + @MyYear     

     WHEN '10' THEN '10/1/' + @MyYear     

     WHEN '11' THEN '11/1/' + @MyYear     

     WHEN '12' THEN '12/1/' + @MyYear     

    End  

   

 SELECT @StartEnd = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@MyDate))),DATEADD(mm,1,@MyDate)),101)     

   

 SET @StartEnd = @StartEnd + ' 23:59:59'   

   

 RETURN @StartEnd   

END