Get beginning of the day datetime -aka today’s midnight – in T-SQL

By default I avoid putting any business logic into SQL. IMO it’s a good rule-of-thumb, but people often argue about it. I forget why it’s good rule, because I use it, but then every once for a while I need to put some little logic into SQL because it’s convenient, then I get remanded why oh why I don’t like it…

It’s a simple case: get datetime exactly at midnight for some given timestamp eg. now. Googling did provide some ridiculous examples of concatenate-strings+convert I don’t like, and I was sure there must be a simple case like DATE(YEAR(@Now),MONTH(@Now),DAY(@Now))… a far as I know there isn’t. So here is a close approximation 😉

CREATE FUNCTION dbo.[ufn_DayStart] (
    @DaysOffset int = 0
)
RETURNS DATETIME
AS
BEGIN

    DECLARE @Now datetime   
    set @Now = GetDate()
    DECLARE @DayStart datetime
    set @DayStart = @Now

    set @DayStart = DATEADD (ms, -DATEPART(ms,@Now),@DayStart)
    set @DayStart = DATEADD (s, -DATEPART(s,@Now),@DayStart)
    set @DayStart = DATEADD (mi, -DATEPART(mi,@Now),@DayStart)
    set @DayStart = DATEADD (hh, -DATEPART(hh,@Now),@DayStart)
    set @DayStart = DATEADD (d, @DaysOffset,@DayStart)

    return @DayStart 
END

… oh it’s have a @DaysOffset for convenience, eg. when you want midnight today and tomorrow 😉

PS. It’s strange I feel like I did stumble upon similar problem few moths back, daja-vu?