List today and the last 7 days
List from 11pm tonight, the last 168 hours
select DateAdd(Hour, -n, DateAdd(HOUR, 23, cast(CAST(GETDATE() AS DATE) as DateTime)))
as [CountDown]
FROM (SELECT TOP 168 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
FROM sys.all_objects) t
Get the current date
select GETDATE() as [GETDATE]
Get the current Date as a Date
select CAST(GETDATE() AS DATE) as [TODAY]
Get the current Date as a DateTime
select cast(CAST(GETDATE() AS DATE) as DateTime) as [TODAY]