36

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

Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
user867621
  • 1,147
  • 4
  • 16
  • 34
  • you mean how much minutes left until midnight? – Dzoki Oct 08 '11 at 22:42
  • no, i have to start_time and a finish_time... the start time needs to be midnight and the finish_time would be like n minutes later – user867621 Oct 08 '11 at 22:45
  • if you know the time then shortest method may be: select cast(convert(varchar,getdate(),23) + ' 23:59:59' as datetime) and you can get start and end date as below: select cast(convert(varchar,getdate(),23) + ' 23:59:59' as datetime) startTime, dateadd(minute,10,cast(convert(varchar,getdate(),23) + ' 23:59:59' as datetime)) endTime – Manpreet Singh Dhillon Apr 12 '21 at 22:18

6 Answers6

53

Date now midnight time: 

Select DATEADD(d,0,DATEDIFF(d,0,GETDATE()))

ADD 600 Minutes: 

Select DATEADD(mi,600,DATEDIFF(d,0,GETDATE()))
Liam
  • 27,717
  • 28
  • 128
  • 190
Carrisi
  • 531
  • 4
  • 3
  • Call me crazy but `DATEADD(d,0,DATEDIFF(d,0,GETDATE()))` gives start of yesterday, not start of today. – tymtam Feb 22 '23 at 22:58
  • `DATEADD(d,0,DATEDIFF(d,0,GETDATE()))` works perfectly fine and yields 12am of the current dau which for today is: `2023-05-23 00:00:00.000` – CajunCoding May 23 '23 at 18:46
33

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
Community
  • 1
  • 1
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • Thank you.. and sorry I didn't search the site better – user867621 Oct 08 '11 at 23:01
  • So does this mean that if I do: SET today = GETDATE(); SET start = DATEADD(DAY, DATEDIFF(DAY, 0, today), 0); it'll be set to midnight of the current day? (couldn't use multiple '@' symbols in comments) ('midnight' of course meaning hour 00:00 of the day) – Alex Watts Jul 21 '16 at 13:45
  • Yes, except your script needed some syntax corrections..... Added edit top my answer to demo – Charles Bretana Jul 21 '16 at 16:26
15

You can also cast to date to strip off the time portion:

declare @minutes int = 600
declare @start datetime = cast(getdate() as date)
declare @finish datetime = dateadd(mi, @minutes, @start)

Although, honestly, I have no idea how it performs compared to

dateadd(day, datediff(day, 0, getdate()), 0)

Jeff Ogata
  • 56,645
  • 19
  • 114
  • 127
  • for me, casting getdate to date did not work, i got an error complaining that "Type date is not a defined system type." - sql server 2005 – Peter Perháč Dec 12 '13 at 14:36
  • 2
    @PeterPerháč `date` was added in SQL Server 2008. – Jeff Ogata Dec 13 '13 at 03:22
  • I compared `cast(getdate() as date)` and `dateadd(d, 0, DATEDIFF(d, 0, getdate()))` with a table of a few thousand rows. **The cast was 10x faster** than the dateadd... – DarthAyokas Mar 12 '18 at 08:42
8

Necromancing:

The simplest/fastest way is: 

SELECT 
    DATEADD(minute, 1, 
        CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime)   
    )

or with UTC:

SELECT 
    DATEADD(minute, 1, 
        CAST(FLOOR(CAST( GETUTCDATE() AS float)) AS datetime)   
    )

This works, because in SQL-server, datetime is a float value, the integer part representing the days from 01.01.1900, the sub-integer part represents the percentage of the full day (24h).

So if you round-down (floor) the floating-point number to a integer, you get the midnight-time.
1900-01-01T00:00:00.000 being 0.

Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
  • 1
    Using the above, I get `2020-04-03 00:01:00.000`. I believe you want a 0 instead of a 1. `SELECT DATEADD(minute, 0, CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime))` – David Metcalfe Apr 04 '20 at 06:27
  • @David Metcalfe: The question was how to add to it - this demonstrates that with +1 minute. – Stefan Steiger Jan 26 '23 at 11:37
2

Use following:

select DATEADD(SECOND, 36000, CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS datetime))

This way you can adjust the seconds to pinpoint any time of day you want.

Please refer to this for date/time formatting in SQL Server: SQL Server Date/Time Formatting

user353gre3
  • 2,747
  • 4
  • 24
  • 27
user1431616
  • 31
  • 2
-1

I had to do something similar, create a procedure to run from a certain time the previous day to a certain time on the current day This is what I did to set the start date to 16:30 on the previous day, basically subtract the parts you don't want to get them back to 0 then add the value that you want it to be.

-- Set Start Date to previous day and set start time to 16:30.00.000

SET @StartDate = GetDate()
SET @StartDate = DateAdd(dd,- 1, @StartDate) 
SET @StartDate = DateAdd(hh,- (DatePart(hh,@StartDate))+16, @StartDate) 
SET @StartDate = DateAdd(mi,- (DatePart(mi,@StartDate))+30, @StartDate) 
SET @StartDate = DateAdd(ss,- (DatePart(ss,@StartDate)), @StartDate) 
SET @StartDate = DateAdd(ms,- (DatePart(ms,@StartDate)), @StartDate)

Hope this helps someone.

Chris Forrence
  • 10,042
  • 11
  • 48
  • 64
Najam
  • 49
  • 1
  • 1
  • 1
    Welcome to Stack Overflow! Please don't post identical answers to multiple questions. Post one good answer, then flag / vote to close the other questions as duplicates. If the question is not a duplicate, *tailor your answers to the question.* Duplicate answer found [here](//stackoverflow.com/a/41609720/2747593). – Scott Weldon Feb 02 '17 at 18:00