user867621 user867621 - 1 year ago 69
SQL Question

get the date and time for today at midnight and add to it

Is there a sql command to get today's date at midnight and be able to add a number of minutes to it?

Answer Source

Yes, just use datediff and dateadd functions to strip the time from any date, then add a fractional portion of a day to that number

Declare @aDate DateTime
Set @aDate = getDate()
Declare @Minutes Integer
Set @minutes = 600 -- 10 hours

Select DateAdd(day, DateDiff(day, 0, @aDate), 0) + @minutes / 1440.0  -- 1440 min/day
 -- or You could also use the dateadd again...
Select DateAdd(minute, @minutes , DateAdd(day, DateDiff(day, 0, @aDate), 0))

Both selects return 10:00 am on the same day (more or less). This works because of, well, check out this SO answer

EDIT: Added sample script to show how this works:

declare @dtTim datetime = getDate()
declare @today datetime = dateAdd(day, dateDiff(day, 0, @dtTim ), 0)
select  @dtTim, @today
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download