How to get date and time for week prior (start & end)

[03Explorer]

I want to generate a datetime field for two variables that have hours as Midnight and 11:59 from current time. Formulas I am using:

SET @StartDate = dateadd(d, -10, getdate() )
SET @EndDate = dateadd(d, -3, getdate() )

Reason for 3 days from current is we are pulling for historical data that is guaranteed to have data 3 days back.

I want start date to have a time of 12:00am and end date to be 11:50pm. This code gives me the days back, but with the same time of current execution time.

 

[markros]

See

http://www.karaszi.com/SQLServer/info_datetime.asp#GettingRidOfTimePortion

 

[03Explorer]

The data I am filtering on is time dependant. So by removing the time would not help much if I want to limit my data to today before noon (as example).

 

[markros]

With the code above you convert to 12 am and then you need to add time (in hours or minutes) to the time you want.

 

[RyanEK]

Hi,

10 Days back @ 12am

select dateadd(dd,0,datediff(dd,0,dateadd(d, -10, getdate() )))

3 Days back @ 11:50pm

select dateadd(mi,-10,datediff(dd,0,dateadd(d, -2, getdate() )))

Ryan