CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005
lethalMango
lethalMango

Reputation: 4491

Insert Tomorrows Date at Specific Time

I am attempting to insert tomorrow's date at 10:00:00 into SQL Server 2005/2008.

I currently have 

DATEADD(dd, 1, GETDATE())

which gives me tomorrow's date fine, however I have tried a number of methods to also concat / set the time to 10:00:00.

Example:

Current Time: 2013-01-07 15:37:05
Required Insert Date: 2013-01-08 10:00:00

Upvotes: 2

Views: 3325

Answers (4)

user985189
user985189

Reputation:

Try this out:

DATEADD(hh,34,DATEDIFF(dd,0,GETDATE()))

Upvotes: 1

Art
Art

Reputation: 5792

This will work in Oracle - returns 10 a.m. of next day:

Select to_char((trunc(Sysdate)+1)+10/24, 'yyyy-mm-dd hh24:ss:mi') insert_date
 From dual
 /

 SQL> 2013-01-08 10:00:00

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

One less DATEADD than the other answers:

SELECT DATEADD(day,DATEDIFF(day,'20010101',GETDATE()),'2001-01-02T10:00:00')

This adds the (integral) number of days since 1st January 2001 onto 10am on the 2nd January 2001.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460208

You can use a combination of DATEADD and DATEDIFF.

SELECT DATEADD(hh,10, DATEDIFF(dd,0, DATEADD(dd, 1, GetDate())))

DATEDIFF(dd,0...) truncates the time part of a date, hence "rounds" to midnight and DATEADD(hh,10...) adds 10 hours.

DEMO

Upvotes: 5

Related Questions