CODEWITHSUNDEEP
sqlsql-server
RAJASEKHARA REDDY MARAM
RAJASEKHARA REDDY MARAM

Reputation: 153

how to get the current date by difference time to current date

i want data between yesterday 7Am to today 7Am, for that i am trying to get today 7AM(2018-11-22 07:00:00 Am) for that i am trying this.

select getdate() as todate,dateadd(hh,-7,getdate()) as sehrsrem,
       datediff(hour,dateadd(hh,-7,getdate()),getdate()) 
from   dump;

By using datediff() i am getting number 7 ,how to get full date.

Thanks.

Upvotes: 1

Views: 923

Answers (2)

Yakov R.
Yakov R.

Reputation: 624

By converting GETDATE() to DATE type we remove the time part. After that all you have to is add 7 hours to get today's desired value and subtract 17 hours to receive yesterdays desired time.

DECLARE @ToDay DATETIME = CONVERT(DATE,GETDATE())

SELECT DATEADD(HH,   7, @ToDay) AS Today
     , DATEADD(HH, -17, @ToDay) AS Yesterday

enter image description here

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

this will gives you the required date time

select  dateadd(day, datediff(day, 0, getdate()), -1) + '07:00' as yesterday_7am,
        dateadd(day, datediff(day, 0, getdate()),  0) + '07:00' as today_7am

to get data between these date use it at WHERE clause

WHERE  date_column >= dateadd(day, datediff(day, 0, getdate()), -1) + '07:00'
AND    date_col    <  dateadd(day, datediff(day, 0, getdate()),  0) + '07:00'

Upvotes: 1

Related Questions