dateAdd inside where clause

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » dateAdd inside where clause

11 posts, Page 1 of 2

1 2 »»

dateAdd inside where clause

Rate Topic

Display Mode

Topic Options

Author

Message

LowFatSpread

Posted Monday, November 24, 2008 9:10 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 02, 2012 4:26 PM
Points: 14, Visits: 39

Hi There,

I was wondering if someone could help me with a query that i'm having issues with. I want to only the records from the join + 7 days in the result.

Can someone take a look at my query and help with the syntax please as i'm not sure if I have used the correct join either.

SELECT s.StoreName, s.StoreID,h.StoreDate,h.OpenTime,h.ClosingTime,h.Comments from schema.mytable2 s
left JOIN schema.mytable1 h on
s.[StoreID] = h.[StoreID]
where
h.[StoreDate] between GetDate() and DateAdd(dd, 1, h.StoreDate)
and s.StoreName = 'store'

store 0001 2008-11-26 00:00:00.000 9:00a 5:30p No Special Events
store 0001 2008-11-27 00:00:00.000 9:00a 9:00p No Special Events
store 0001 2008-11-28 00:00:00.000 9:00a 5:30p No Special Events
store 0001 2008-11-29 00:00:00.000 9:00a 5:30p No Special Events

Post #608017

RBarryYoung

Posted Monday, November 24, 2008 9:32 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

This statement:

I want to only the records from the join + 7 days in the result.

is not intelligible.

Please provide sample data and examples of the result set that you want.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #608023

LowFatSpread

Posted Monday, November 24, 2008 11:29 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 02, 2012 4:26 PM
Points: 14, Visits: 39

HI There,

Sorry about the low brow post it's been a long week. I have attached some sample data and table structures. Basically what i'm trying to do is return a list of store hours that exist in the database that are no more than 7 days from today inclusive.I need to pass the storename in a parameter.

Sean - thanks in advance

!--- table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [tblhours](
[StoreID] [int] NULL,
[StoreDate] [datetime] NULL,
[OpenTime] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClosingTime] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comments] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

!---- sample data

4902 2008-11-25 00:00:00.000 10:00a 5:30p comments
4902 2008-11-26 00:00:00.000 10:00a 5:30p comments
4902 2008-11-27 00:00:00.000 10:00a 5:30p comments
4902 2008-11-28 00:00:00.000 10:00a 5:30p comments

4903 2008-11-25 00:00:00.000 10:00a 5:30p comments
4903 2008-11-26 00:00:00.000 10:00a 5:30p comments
4903 2008-11-27 00:00:00.000 10:00a 5:30p comments
4903 2008-11-28 00:00:00.000 10:00a 5:30p comments

!------------------------- table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [stores](
[StoreID] [int] NOT NULL,
[StoreName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_stores] PRIMARY KEY CLUSTERED
(
[StoreID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

!--------------------------------- sample data

4903 storeone
4903 storetwo

Post #608089

Vladan

Posted Tuesday, November 25, 2008 1:08 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 05, 2011 1:38 AM
Points: 1,636, Visits: 604

Hi,
as far as I can tell, LEFT is unnecessary here - in fact, the query performs as with INNER JOIN, because there is condition in the WHERE clause that works with the joined table. Therefore, I would suggest using inner join to make it visible on first glance.

Something like this should work:

SELECT s.StoreName, s.StoreID, h.StoreDate, h.OpenTime, h.ClosingTime, h.Comments 
FROM stores s
JOIN tblhours h ON s.[StoreID] = h.[StoreID]
WHERE h.[StoreDate] >= DATEADD(d,-7,GETDATE())
	AND h.[StoreDate] <= GETDATE()
	AND s.StoreName = 'store'

The conditions in WHERE clause depend on what precisely is meant by maximum 7 days (i.e. included today-7 or not?), whether StoreDate has always time-part 00:00:00 or not etc. Since you posted data with no time, I didn't bother with it - it may require some changes before the query works as you expect.
I mostly avoid BETWEEN when working with time data, because often the right solution includes just one of the limits - not both. For example, if your dates include time, and you want to show data from one month, it is best to do it as >= (first day of month) AND < (first day of next month).
If you have any problems reaching the result you need, post back with more info about it.

Post #608135

SwePeso

Posted Tuesday, November 25, 2008 2:24 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 2,359, Visits: 3,293

SELECT s.StoreName,
s.StoreID,
h.StoreDate,
h.OpenTime,
h.ClosingTime,
h.Comments
from schema.mytable2 as s
left JOIN schema.mytable1 as h on h.[StoreID] = s.[StoreID]
AND h.[StoreDate] between GetDate() and DateAdd(dd, 7, h.StoreDate)
WHERE s.StoreName = 'store'

N 56°04'39.16"
E 12°55'05.25"

Post #608160

RBarryYoung

Posted Tuesday, November 25, 2008 8:51 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

Either Vladan's or Peso's queries should do it for you (I think that Vladan's "-7" should probably be "+7", but I am not sure). If they are not sufficient, please let us know.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #608424

LowFatSpread

Posted Tuesday, November 25, 2008 12:37 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 02, 2012 4:26 PM
Points: 14, Visits: 39

Hi Guys,

Thanks for the responses.I think the Peso's answer is a little more what I need. As there is only one month of data at a time I'm only interested in showing the dates available from today.

I appreciate the help from all of you.

Sean

Post #608628

Vladan

Posted Thursday, November 27, 2008 5:39 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 05, 2011 1:38 AM
Points: 1,636, Visits: 604

rbarryyoung (11/25/2008)

I think that Vladan's "-7" should probably be "+7", but I am not sure

That depends whether you need data from the last 7 days (like if you need to find what projects were finished last week), or from the next 7 days (beginning today and going into future... like if you make a list of goods that should be delivered during next week).
My query is for the last 7 days. It is different from the one posted by Peso, because I subtract 7 days from today and Peso adds 7 days to the date stored in a row. Otherwise they are almost identical.

In my opinion, it is better for performance to subtract 7 days from a parameter (here: GETDATE()) once for the whole query, than to add 7 days to every row in the table.
I tested this on a large table in my DB.

SELECT count(*)
FROM table T
WHERE T.date > dateadd(dd,-7,GETDATE())

uses index seek and is immediate, while

SELECT count(*)
FROM table T
WHERE GETDATE() > dateadd(dd,7, T.date)

uses index scan and takes some 3 seconds.

The difference is negligible on small tables, but if you want to write well-performing SQL, this is one of the things that can be helpful.

Post #609724

RBarryYoung

Posted Thursday, November 27, 2008 10:03 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376

Vladan (11/27/2008)

rbarryyoung (11/25/2008)

I think that Vladan's "-7" should probably be "+7", but I am not sure

Right, that is what I assumed that you intended, and if you look at the OP's last follow-up post, they do say "what i'm trying to do is return a list of store hours that exist in the database that are no more than 7 days from today inclusive." So I would think that you really wanted +7 instead.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #609900

Vladan

Posted Friday, November 28, 2008 12:52 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 05, 2011 1:38 AM
Points: 1,636, Visits: 604

OK, thanks Smile

. I'm not a native speaker, so I didn't catch that... for some reason I thought that previous 7 days are required. BTW, what does "store hours" mean? Originally I supposed it has to do with storage (how long something is stored in a warehouse), now it seems more like it is the time when a store (e.g. supermarket) will be open.

Post #610130

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions