Replace Dates in query

Hi,

pls try

SELECT DATEADD(d,-1-(DATEPART(dw, GETDATE()) % 7),GETDATE()) AS Last_Friday
      ,DATEADD(d,-1-(DATEPART(dw,GETDATE()) % 7)+7,GETDATE()) AS This_Friday

Select allOpen in new window

Regards

Hello Rgonzo,
Thanks but ,how do  iget date in place of the respective Friday's in the query  and also need to keep the format of Date similar to mentioned in the forst query.

Cheers

Hello,

Last Friday:

SELECT CAST(DATEADD(day, 4, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)) AS DATE)

This Friday:

   Select CAST(DATEADD(day, 11, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)) AS DATE

Can anyone help on how to embed it in the above query.

Cheers

then try

DECLARE @Last_Friday DATETIME = DATEADD(d,-1-(DATEPART(dw,GETDATE()) % 7),GETDATE())
DECLARE @This_Friday as datetime = DATEADD(d,-1-(DATEPART(dw,getdate()) % 7)+7,GETDATE())
;WITH CTE_OnlyDates
AS
(      SELECT GuestName, ArrivalDate, DepartureDate 
       FROM CORRESPONDENCE
       WHERE ISDATE(DepartureDate) = 1
)
SELECT GuestName, ArrivalDate, DepartureDate 
FROM CTE_OnlyDates
WHERE CONVERT(date,ArrivalDate,120) >= '01-01-2016'
       AND ((DepartureDate >= CONVERT(date,@Last_Friday,120))
       AND (CONVERT(date,ArrivalDate,120) >=CONVERT(date,@Last_Friday,120)  AND CONVERT(date,ArrivalDate,120) <  CONVERT(date,@This_Friday,120) )
    OR (CONVERT(date,ArrivalDate,120) < CONVERT(date,@Last_Friday,120) AND DepartureDate >= CONVERT(date,@This_Friday,120) 
)

Select allOpen in new window

ALTER PROCEDURE  [dbo].[SP_RptTravelWeekly]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  





DECLARE @Last_Friday DATETIME = DATEADD(d,-1-(DATEPART(dw,GETDATE()) % 7),GETDATE())
DECLARE @This_Friday as datetime = DATEADD(d,-1-(DATEPART(dw,getdate()) % 7)+7,GETDATE())
;WITH CTE_OnlyDates
AS
(      SELECT GuestName, ArrivalDate, DepartureDate 
       FROM CORRESPONDENCE
       WHERE ISDATE(DepartureDate) = 1
)
SELECT GuestName, ArrivalDate, DepartureDate 
FROM CTE_OnlyDates
WHERE CONVERT(date,ArrivalDate,120) >= '01-01-2016'
       AND ((DepartureDate >= CONVERT(date,@Last_Friday,120))
       AND (CONVERT(date,ArrivalDate,120) >=CONVERT(date,@Last_Friday,120)  AND CONVERT(date,ArrivalDate,120) <  CONVERT(date,@This_Friday,120) )
    OR (CONVERT(date,ArrivalDate,120) < CONVERT(date,@Last_Friday,120) AND DepartureDate >= CONVERT(date,@This_Friday,120)))



	END 

Select allOpen in new window

Got Error:

Msg 241, Level 16, State 1, Procedure SP_RptTravelWeekly, Line 22
Conversion failed when converting date and/or time from character string.

Sorry can't help further

RIAS, first lets try without any convert function:

ALTER PROCEDURE  [dbo].[SP_RptTravelWeekly]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  

DECLARE @Last_Friday AS DATE = DATEADD(d,-1-(DATEPART(dw,GETDATE()) % 7),GETDATE())
DECLARE @This_Friday as date = DATEADD(d,-1-(DATEPART(dw,getdate()) % 7)+7,GETDATE())
;WITH CTE_OnlyDates
AS
(      SELECT GuestName, ArrivalDate, DepartureDate 
       FROM CORRESPONDENCE
       WHERE ISDATE(DepartureDate) = 1
)
SELECT GuestName, ArrivalDate, DepartureDate 
FROM CTE_OnlyDates
WHERE ArrivalDate >= '01-01-2016'
       AND ((DepartureDate >= @Last_Friday)
       AND (ArrivalDate >= @Last_Friday AND ArrivalDate <  @This_Friday)
    OR (ArrivalDate < @Last_Friday AND DepartureDate >= @This_Friday))

Select allOpen in new window

Vitor,

Thanks Please let me convert that nvarchar ro date.
Will definitely get back

Vitor,
Now that i have change the datatype to Date :

This query error:

Argument data type date is invalid for argument 1 of isdate function.

Argument data type date is invalid for argument 1 of isdate function.

I guess that because you changed the data type for that column during your previous question, right?

yup

Cheers!