t-sql using DateAdd function on column with datetime datatype

Hi experts,

This is related to the queries in this ticket.
https://www.experts-exchange.com/questions/29096348/using-t-sql-date-function-to-get-records-older-than-10-days.html?anchor=a42544980¬ificationFollowed=206809480#a42544980

I'm using sql server 2008

I have a table that looks like this called Orders2:

Notice that there are 2 records for 4/16/2018.

Table2.PNG
This is the script for this table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders2](
	[OrderID] [int] NULL,
	[LastName] [nvarchar](255) NULL,
	[FirstName] [nvarchar](255) NULL,
	[OrderDate] [datetime] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (1, N'Smith', N'Sam', CAST(N'2018-04-16T20:10:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (2, N'Johnson', N'Tony', CAST(N'2018-04-16T00:00:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (3, N'Williams', N'Anna', CAST(N'2018-03-27T21:23:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (4, N'Westbrook', N'Kyle', CAST(N'2018-03-27T00:00:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (5, N'George', N'Norman', CAST(N'2018-03-26T23:21:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (6, N'Arnold', N'Laura', CAST(N'2018-03-26T23:21:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (7, N'Vanscyoc', N'Maria', CAST(N'2018-03-26T00:00:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (8, N'Thompson', N'Joe', CAST(N'2018-03-26T00:00:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (9, N'Earnshaw', N'Peter', CAST(N'2018-03-25T08:12:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (10, N'Steele', N'Bill', CAST(N'2018-03-25T08:10:00.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (11, N'Felton', N'Samantha', CAST(N'2017-10-23T09:10:13.000' AS DateTime))
INSERT [dbo].[Orders2] ([OrderID], [LastName], [FirstName], [OrderDate]) VALUES (12, N'Johnson', N'Erica', CAST(N'2017-10-10T15:23:16.000' AS DateTime))

Open in new window


I'm trying to return all the records that are 9 days or older.

If i run this query

DECLARE @TestDate as varchar(50)
SET @TestDate = '2018-04-16 20:10:00'
SELECT DATEDIFF(day, @TestDate, GETDATE()) AS 'Duration'  

I get this:

Check1.PNG
If i run this query

DECLARE @TestDate as varchar(50)
SET @TestDate = '2018-04-16 00:00:00'
SELECT DATEDIFF(day, @TestDate, GETDATE()) AS 'Duration'  

I also get this:

Check1.PNG

Now i try my query on my table.
This is my query:

----- only return records that are 9 days or older
DECLARE @NumberOfDays as int
SET @NumberOfDays = 9

SELECT [OrderID]
      ,[LastName]
      ,[FirstName]
      ,[OrderDate]
FROM [TestDatabase].[dbo].[Orders2] o
WHERE  O.OrderDate <= CAST(DATEADD(DAY, -@NumberOfDays, GETDATE()) AS DATE)
ORDER BY o.[OrderDate] DESC

When i run it, i get this. Notice , it only got one of the records from 4/16/2018.
It didn't return the record for Sam Smith. I believe it's because he has a time and Tony Johnson doesn't.


Query-result.PNG
How do i fix my query? So that it doesn't matter if the date has a time on it or not.
The query for 9 days or older should return both records in 4/16/2018.
LVL 1
maqskywalkerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Snarf0001Commented:
Why not just use a datediff function?
That will typically strip the time component off.

DECLARE @NumberOfDays as int
SET @NumberOfDays = 9

select OrderID, LastName, FirstName, OrderDate
from Orders2 o
where datediff(dd, o.OrderDate, getdate()) >= @NumberOfDays

Open in new window

_agx_Commented:
If you want to include records that are 9 days old in the results, you need to decrease the @NumberOfDays by -1:

        DECLARE @NumberOfDays as int
        SET @NumberOfDays = 8

Then the result of the DATEADD is April 17th at midnight:

          '2018-04-17'

Then change the expression find records less than that date.  

        WHERE  O.OrderDate < CAST(DATEADD(DAY, -@NumberOfDays, GETDATE()) AS DATE)

Then the query will include all records dated of 2018-04-16, no matter the time.  So say today is 2018-04-25. The expression translates to:

        WHERE  O.OrderDate < '2018-04-17'   -- Includes all records dated on 2018-04-16

While DateDiff() would work, it can prevent the database from using any indexes on the OrderDate columns, so better to stick this type of date comparison.

Full Query:
DECLARE @NumberOfDays as int
SET @NumberOfDays = 8

SELECT [OrderID]
      ,[LastName]
      ,[FirstName]
      ,[OrderDate]
FROM [dbo].[Orders2] o
WHERE  O.OrderDate < CAST(DATEADD(DAY, -@NumberOfDays, GETDATE()) AS DATE)
ORDER BY o.[OrderDate] DESC

Open in new window

Mark WillsTopic AdvisorCommented:
If you want to leave @numberofdays as is, simply change the test in the WHERE clause


WHERE  O.OrderDate < CAST(DATEADD(DAY, 1-@NumberOfDays, GETDATE()) AS DATE)

Casting as DATE gives you YYYYMMDD format, so it needs to be a day greater for any datetimes that have hours/minutes/seconds that would be otherwise falling on that date, so, needs to be a day greater and then use the less than comparison.

does that make sense ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
>> If you want to leave @numberofdays as is

@Mark WillsTopic - Well, if you're going to be logical about it ....  ;-)  Seriously though, that's a good way of explaining it.
maqskywalkerAuthor Commented:
thanks. good stuff.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.