Handy Date/Time UDFs

[TJRTech]

Useful user-defined functions for massaging a date/time into TwelveAM and Midnight (actually 3ms before midnight):

Comments?

-- ** Midnight **
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Midnight]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Midnight]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION [dbo].[Midnight] (@dt datetime)  
RETURNS datetime  AS  
BEGIN 
  declare @ret_dt datetime
  set @ret_dt = dateadd(ms, -3, dateadd(dd, 1, convert(datetime, convert(varchar(11), @dt))))
  return @ret_dt
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



-- ** TwelveAM **
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TwelveAM]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[TwelveAM]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION [dbo].[TwelveAM] (@dt datetime)  
RETURNS datetime  AS  
BEGIN 
  declare @ret_dt datetime
  set @ret_dt = convert(datetime, convert(varchar(11), @dt))
  return @ret_dt
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO