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?
A cleaner solution to this problem would be SELECT DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), -1)