CODEWITHSUNDEEP
sql-servergetdate
Alex
Alex

Reputation: 167

How to select data using getdate() + time?

I have this code:

select * 
from Table
where [Date] >= cast(getdate() - 1 as date) 
  and [Date] < cast(getdate() + 1 as date)
order by [Date] desc

and I need to change this part:

where [Date] >= cast(getdate() - 1 as date)

that the search start at 02:00 AM + getdate() - 1,

Something like this:

where [Date] >= getdate() - 1, 02:00:00

Upvotes: 1

Views: 1657

Answers (3)

Charlieface
Charlieface

Reputation: 71579

You can use some basic time arithmetic for this: subtract 22 hours, after stripping off the time

WHERE [Date] >= DATEADD(hour, -22, CAST(CAST(GETDATE() AS date) AS datetime))

You need to cast back to datetime otherwise you cannot add a time.

Upvotes: 1

RF1991
RF1991

Reputation: 2265

where [Date] >=cast(concat(cast(DATEADD(day, -1, CAST(GETDATE() AS date)) as varchar(50)),' 02:00:00') as datetime)

Upvotes: 0

Stu
Stu

Reputation: 32599

In SQL Server with the datetime type you can simply concatenate the time portion after first removing it. Try

where [Date] > DateAdd(dd, DateDiff(dd, 1, GetDate()), 0) + '02:00:00'

Upvotes: 1

Related Questions