SQL DateAdd() Timestamp

Like this you mean?

select
        CONVERT(VARCHAR(11), @StartOfPrevWeek-1,109) as FromDate,
        CONVERT(VARCHAR(11), @EndOfPrevWeek,109) as ToDate,

I think so - does that just take out the timestamp and give me all the gifts between the specified days?

You can use the following function to get only Date value

CREATE FUNCTION [dbo].[GetDateOnly] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    RETURN CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)

END


regards

Dani

Well it depends on the whole query really. I didn't see a statement in there that grabs data from a table. Modifying this selet truncates the time stamp from the datetime. If you re-cast is back to a datetime you'll see it will not have a time element.

Sorry - all the necessary pieces of the query are below:

-- all tribute gifts for any honoree with a most recent gift date 
-- in the previous week (Sunday to Saturday)

use VCU
-- begin declare variables for gifts in last week (Monday to Sunday)

DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime
 
--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+5), GetDate())

-- end declare variables for gifts in last week (Monday to Sunday)

select 
	CONVERT(VARCHAR(11), @StartOfPrevWeek-1,101) as FromDate,
	CONVERT(VARCHAR(11), @EndOfPrevWeek,101) as ToDate,
	donorgifts.giftprocdt as ProcessDate

from gifts_VIEW_donor_relations donorgifts

where donorgifts.giftprocdt between CONVERT(VARCHAR(11), @StartOfPrevWeek-1,7) -- gift is in last full week
		AND CONVERT(VARCHAR(11), @EndOfPrevWeek,7)

Select allOpen in new window

Alter the last select to:

select
        cast(CONVERT(VARCHAR(10), @StartOfPrevWeek-1,101) as datetime) as FromDate,
        cast(CONVERT(VARCHAR(10), @EndOfPrevWeek,101)  as datetime) as ToDate,
        donorgifts.giftprocdt as ProcessDate

from gifts_VIEW_donor_relations donorgifts

Is your last post (with the CAST command) just to show the time as 00:00:00 but the actual removal of the timestamp is done with your first post?  I ask because the query was slowed down considerably with the CAST command.  I am hoping I don't need it.

It's best to do the selection with the correct format so it your fields you're comparing are datetime fields it makes sense to cast otherwise you rely on SQL's implicit casting which may (or may not) get it wrong.

The removal of the time portion is done when you convert to the mm/dd/yyyy string which is set to 10 characters in length. This method relies on SQL converting the date time to the format you requested which was 101, which does include the time portion, and then outputting that into a 10 character string. This effectively truncates the time off. When you cast it back to a datetime, the time portion is set by SQL to midnight, so the removal is done when you force the longer source string into a shorter target varchar. It's a neat trick I've used for years and considering all the ways there are of removing the time portion I have always found this way reliable, fast, easy to read and quick to code.

Lee