• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 59
  • Last Modified:

using t-sql date function to get records older than 10 days

i'm using sql server 2008

I have a table that looks like this:

p1.PNG
This is the script to create this table.

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders1](
	[OrderID] [int] NULL,
	[CustomerID] [nvarchar](255) NULL,
	[EmployeeID] [int] NULL,
	[OrderDate] [datetime] NULL,
	[Freight] [decimal](20, 2) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11074, N'SIMOB', 7, CAST(N'2018-04-24T09:34:00.000' AS DateTime), CAST(18.44 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11075, N'RICSU', 8, CAST(N'2018-04-23T08:30:00.000' AS DateTime), CAST(6.19 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11076, N'BONAP', 4, CAST(N'2018-04-23T07:10:00.000' AS DateTime), CAST(38.28 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11077, N'RATTC', 1, CAST(N'2018-04-20T08:20:00.000' AS DateTime), CAST(8.53 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11070, N'LEHMS', 2, CAST(N'2018-04-15T10:23:00.000' AS DateTime), CAST(136.00 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11071, N'LILAS', 1, CAST(N'2018-04-14T08:00:00.000' AS DateTime), CAST(0.93 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11072, N'ERNSH', 4, CAST(N'2018-04-14T06:34:00.000' AS DateTime), CAST(258.64 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11073, N'PERIC', 2, CAST(N'2018-04-12T01:00:00.000' AS DateTime), CAST(24.95 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11067, N'DRACD', 1, CAST(N'2018-04-11T09:00:00.000' AS DateTime), CAST(7.98 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11068, N'QUEEN', 8, CAST(N'2018-04-10T08:22:00.000' AS DateTime), CAST(81.75 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11069, N'TORTU', 1, CAST(N'2018-04-10T08:20:00.000' AS DateTime), CAST(15.67 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11064, N'SAVEA', 1, CAST(N'2018-04-09T09:40:00.000' AS DateTime), CAST(30.09 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11065, N'LILAS', 8, CAST(N'2018-04-08T00:00:00.000' AS DateTime), CAST(12.91 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11066, N'WHITC', 7, CAST(N'2018-04-07T09:20:00.000' AS DateTime), CAST(44.72 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11060, N'FRANS', 2, CAST(N'2018-04-06T00:00:00.000' AS DateTime), CAST(10.98 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11061, N'GREAL', 4, CAST(N'2018-04-05T05:34:00.000' AS DateTime), CAST(14.01 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11062, N'REGGC', 4, CAST(N'2018-04-04T05:10:00.000' AS DateTime), CAST(29.93 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11063, N'HUNGO', 3, CAST(N'2018-04-03T04:50:00.000' AS DateTime), CAST(81.73 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11057, N'NORTS', 3, CAST(N'2018-04-02T02:32:00.000' AS DateTime), CAST(4.13 AS Decimal(20, 2)))
GO
INSERT [dbo].[Orders1] ([OrderID], [CustomerID], [EmployeeID], [OrderDate], [Freight]) VALUES (11058, N'BLAUS', 9, CAST(N'2018-04-01T02:30:00.000' AS DateTime), CAST(31.14 AS Decimal(20, 2)))
GO

Open in new window


What's the most efficient query to return all orders whose OrderDate is older than 10 days from today's date?
So get today's date form using date function and return all orders older than 10 days.
0
maqskywalker
Asked:
maqskywalker
2 Solutions
 
Nitin SontakkeDeveloperCommented:
Try following:

select *, datediff(dd, [OrderDate], getdate()) [OrderAgeInDays]
from [dbo].[Orders1]
where 1 = 1
	and datediff(dd, [OrderDate], getdate()) > 10

Open in new window

0
 
ste5anSenior DeveloperCommented:
I don't have SQL Server 2008 add hands, but generally you would use a sargeable predicate, thus avoiding calculations with columns. So that indices could be used.

SELECT *
FROM   Orders1 O
WHERE  O.OrderDate < CAST(DATEADD(DAY, -10, GETDATE()) AS DATE);

Open in new window

But the newer SQL Server versions can optimize such date queries:

SELECT * ,
       DATEDIFF(dd, O.OrderDate, GETDATE()) AS [OrderAgeInDays]
FROM   Orders1 O
WHERE  DATEDIFF(dd, O.OrderDate, GETDATE()) > 10;

Open in new window

so that they get the same plan.

Thus you need to compare the actual outcome.

But depending on the table size, you will get a table (index) scan, cause you use the asterisk, returning all columns.
0
 
Scott PletcherSenior DBACommented:
ste5an's got it; his first method is perfect.
0
 
maqskywalkerAuthor Commented:
Thanks guys. Great job.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now