TSQL - DATEDIFF() FUNCTION

Hi Darius,
Please try like below-

DECLARE @StartDate DATETIME = '2017-01-16'

DECLARE @EndDate DATETIME = DATEADD(d,-7,@StartDate) 

SELECT @StartDate , @EndDate

Select allOpen in new window

Output

/*------------------------
DECLARE @StartDate DATETIME = '2017-01-16'

DECLARE @EndDate DATETIME = DATEADD(d,-7,@StartDate) 

SELECT @StartDate , @EndDate
------------------------*/
                        
----------------------- -----------------------
2017-01-16 00:00:00.000 2017-01-09 00:00:00.000

(1 row(s) affected)

Select allOpen in new window

You need to try like

--

SELECT * FROM TABLE 
WHERE DATECOLUMN BETWEEN @StartDate AND @EndDate

--

Select allOpen in new window

Hope it helps!

You don't need DATEDIFF or any variable:

SELECT *
FROM TableName
WHERE dateAdded BETWEEN GETDATE()-7 and GETDATE()

Select allOpen in new window

Pawan,
my understand  set below gives 7 days in past from provided date.

DECLARE @OrderDate DATETIME  = '2017-01-16';            
DECLARE @EndDate DATETIME = DATEADD(d,-7,@OrderDate)


It does not matter what user going to select in @OrderDate! Like I mention I need to make sure the date records in where clause is not today date and is not older when 7 days.

I like to reduce big bunch of records of old records from table...  and not today date as well!

records in where clause is not today date and is not older when 7 days.

SELECT *
FROM TableName
WHERE dateAdded BETWEEN CAST(GETDATE()-7 AS DATE) and CAST(GETDATE()-1 AS DATE)

Select allOpen in new window

Example pls?

Using my example you will get records where Datecolumn is between todaysdate-7 AND todaysDate

Dates you increase of decrease using like DATEADD(d,-7,@OrderDate)  --- (@OrderDate-7 )
DATEADD(d,-6,@OrderDate)  --- (@OrderDate-6 days )
DATEADD(d,-5,@OrderDate)  --- (@OrderDate-5 days )
..
..
..
DATEADD(d,-1,@OrderDate)  --- (@OrderDate-1 day )
etc...

Do you need something else?

Thank you Pawan!

Thank you Vitor!

Welcome Darius !!

Guys,
 
Is it good idea to do this way?

DECLARE @OrderDate datetime	= '2017-01-16';  -- PROVIDED DATE 
DECLARE @DATESET   datetime	 set @DATESET = 
	(	select top 1 dateadded From Documents 
		where dateadded between CAST(GETDATE()-7 AS DATE) and CAST(GETDATE()-1 AS DATE) )

SELECT * FROM Documents
WHERE @DATESET is not NULL AND CAST(dateAdded AS DATE) = @OrderDate

Select allOpen in new window

Hi Darius,
Please try like below-

DECLARE @OrderDate datetime	= '2017-01-16';  -- PROVIDED DATE 
DECLARE @DATESET   datetime	 

Select top 1 @DATESET = dateadded From Documents where dateadded between CAST(GETDATE()-7 AS DATE) and CAST(GETDATE()-1 AS DATE))

SELECT * FROM Documents
WHERE @DATESET is not NULL AND dateAdded = @OrderDate

Select allOpen in new window

Hope it helps!

Hi Darius,
You can also do like below -

DECLARE @OrderDate datetime	= '2017-01-16';  -- PROVIDED DATE 
DECLARE @DATESET   datetime	 

Select top 1 @DATESET = dateadded From Documents where ( dateadded between CAST(GETDATE()-7 AS DATE) and CAST(GETDATE()-1 AS DATE)) )
AND ( dateadded IS NOT NULL )

SELECT * FROM Documents
WHERE dateAdded = @OrderDate

Select allOpen in new window

Hope it helps

Darius, can you explain why the following doesn't meet your requirement?

SELECT *
FROM Documents
WHERE dateAdded >= CAST(GETDATE()-7 AS DATE) and dateAdded < CAST(GETDATE() AS DATE)

Select allOpen in new window

Vitor, I can  see you proposed to use IF statment...

 I like to ask what if I like to use different table when is dates records for today date

DECLARE @OrderDate datetime	= '20170124';  -- PROVIDED DATE 
DECLARE @DATESET   datetime
DECLARE @ISTODAY   datetime	 

select top 1 @DATESET = dateadded 
From Documents1
where dateadded between CAST(GETDATE()-7 AS DATE) and CAST(GETDATE()-1 AS DATE)

select top 1 @ISTODAY= dateadded 
From Documents2
where dateadded = GETDATE()

IF @DATESET is not NULL 
	SELECT * FROM Documents
	WHERE CAST(dateAdded AS DATE) = @OrderDate
ELSE
-- GET RESULTS IF DATE TODAY AND USER PROVIDED TODAY DATE

Select allOpen in new window

Yes, you can use IF in a T-SQL script.

I'm just trying to understand your requirements because you said: "And I need to reduce records found in WHERE clause which date is not older when 7 days and not today date" and then your code shows that you only want to return the today's date:

DECLARE @OrderDate datetime	= '20170124';  -- PROVIDED DATE
(...)
IF @DATESET is not NULL 
	SELECT * FROM Documents
	WHERE CAST(dateAdded AS DATE) = @OrderDate

Select allOpen in new window

Why is that?

Vitor,

 Need to reduce amount of checked records (system is really busy)

required if :
1. Customer provided today date!
         (Documents with today  timestamp will be reviewed from  Table1  )
2. Customer provided older date! (not today but not older when 7 days)!  
         Documents with these timestamps will be reviewed from Table2

Need to reduce amount of checked records (system is really busy)

There are other better methods for that. What you're doing here may not help at all so I would try to go with a simple solution and then realize about the performance (if too slow come back so we can try to tune it). Here's my suggestion:

DECLARE @OrderDate datetime	= '20170124';  -- PROVIDED DATE 

IF CAST(GETDATE() AS DATE) = @OrderDate
-- GET RESULTS IF DATE TODAY AND USER PROVIDED TODAY DATE
	BEGIN
		select *
		From Documents1
		where dateadded > CAST(GETDATE() AS DATE)-1 AND dateadded < CAST(GETDATE() AS DATE)+1

		select *
		From Documents2
		where dateadded > CAST(GETDATE() AS DATE)-1 AND dateadded < CAST(GETDATE() AS DATE)+1
	END
ELSE
-- -- GET RESULTS IF USER PROVIDED OLDER DATE
	BEGIN
		select *
		From Documents1
		where dateadded >= CAST(GETDATE()-7 AS DATE) and dateadded < CAST(GETDATE() AS DATE)

		select *
		From Documents2
		where dateadded >= CAST(GETDATE()-7 AS DATE) and dateadded < CAST(GETDATE() AS DATE)
	END

Select allOpen in new window

FYI,
   Table 1 has only today records
    and
   Table 2 has records older when today date (all records of today on table 1 will be move to table 2 tomorrow)

I think this is what I need!

IF CAST(GETDATE() AS DATE) = @OrderDate
-- GET RESULTS IF DATE TODAY AND USER PROVIDED TODAY DATE
	BEGIN
		select *
		From Documents1
		where dateadded > CAST(GETDATE() AS DATE)-1 AND dateadded < CAST(GETDATE() AS DATE)+1
	END
ELSE
-- -- GET RESULTS IF USER PROVIDED OLDER DATE
	BEGIN
		select *
		From Documents2
		where dateadded >= CAST(GETDATE()-7 AS DATE) and dateadded < CAST(GETDATE() AS DATE)
	END

Select allOpen in new window

It will also work. I just adjusted the filtering based on your last comment about having 2 tables.
Please let me know about the performance.

Thank you guys for your time and patience !!!

Much appreciate!