CODEWITHSUNDEEP
sqlsql-serversql-server-2008datetimedateadd
Colin O'Donoghue
Colin O'Donoghue

Reputation: 27

Using DATEADD and DATEDIFF truncates the time

I am using the following to get to get the 1st day of next month with time:

select DATEADD(m, DATEDIFF(m, -1, getdate()), 0)

But the output is:

 2018-12-01 00:00:00.000

And the expected result is:

 2018-12-01 11:53:30.677

I have tried various approaches but not able to get required output. I am using SQL Server 2008.

Upvotes: 0

Views: 160

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272256

Subtract DAY(@date) - 1 days from @date to get first day of that month including time. Then add one month:

SELECT DATEADD(MONTH, 1, DATEADD(DAY, -DAY(GETDATE()) + 1, GETDATE()))
-- 2018-12-01 04:52:33.403

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270473

You can add two datetime values, one for the date and the other for the time:

select DATEADD(month, DATEDIFF(month, -1, getdate()), 0) + cast(cast(getdate() as time) as datetime)

I am guessing that you want the time value from the current time.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

try like below

select DATEADD(m, DATEDIFF(m, -1, getdate()), 0)+  convert(DATETIME,'11:53:30.677')

in case of current time it would be like below

select DATEADD(m, DATEDIFF(m, -1, getdate()), 0)+  convert(datetime, convert(time,getdate()))

Upvotes: 0

Related Questions