dateadd confusion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

dateadd confusion

Author

Topic

LoriM
Starting Member

29 Posts

Posted - 2005-08-10 : 12:27:24

I am trying to find a group of clients that were entered last week. I am getting all the dates to go with the Monday of the week they were entered. i.e. If they were entered on 8-2 or 8-3, they go with 8-1. If they were entered, 8-9, they will be under the 8-8 Monday.

No problem with finding the Mondays using either the nice 'weekcommence' function I found here or using numerous t-sql examples.

One thing I don't understand is- how come I can't use an equal sign in my syntax?

For instance,

SELECT dbo.weekcommence(date_added)
FROM clients
where dbo.weekcommence(date_added) = dateadd(wk, -2, GETDATE() )

won't return any results. If I use a greater than sign, I get this week, 8-8 and last week 8-1. I only want to see the week of 8-1.

Now- look at what I have below: Shouldn't I really be able to see ONLY last week's Mondays with this?

select dateadd(wk, -2, dbo.weekcommence(date_added))from clients

To me- the way this reads is: show me 2 weeks ago, from these dates (first Mondays function).
The results are the last two MONTHS, not weeks. What am I misinterpreting?

I can also use this:

SELECT dbo.weekcommence(date_added) FROM clients
WHERE (dbo.weekcommence(date_added) >= dateadd("d",-7,DATEADD(wk, DATEDIFF(wk,1,getdate()), 0)))

and get the week's of 8-1 and 8-8. But I can't get JUST 8-1 if I take out the greater than sign.

To me- this one reads: Select all my Mondays from the function. Show me Mondays from my function that are = 7 days from this Monday's date.

It seems like it should be straightforward, but I'm obviously missing something big. Any help?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-10 : 12:55:46

I an not familiar with the weekcommence function you mentioned, and I couldn't find it in the search. The code below should do the same thing.

You are better off searching a date range, because it will allow you to use an index on date_added, if it exists. If you run it through a function, SQL Server will ignore the index, or at best, do a full scan of the index.

The code below is adapted from the code on this topic:
Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

SELECT
	Week_Added = dateadd(dd,(datediff(dd,-53690,a.date_added)/7)*7,-53690)
FROM
	clients a
where
	a.date_added >= dateadd(dd,(datediff(dd,-53690, dateadd(dd,-14,GETDATE()) )/7)*7,-53690) and
	a.date_added <  dateadd(dd,(datediff(dd,-53690, dateadd(dd,-7,GETDATE()) )/7)*7,-53690)

or using the F_START_OF_WEEK function

SELECT
	Week_Added = dbo.F_START_OF_WEEK(a.date_added,2)
FROM
	clients a
where
	a.date_added >= dateadd(dd,-14,dbo.F_START_OF_WEEK(GETDATE(),2))	and
	a.date_added <  dateadd(dd,-7,dbo.F_START_OF_WEEK(GETDATE(),2))

CODO ERGO SUM

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-10 : 13:12:32

"One thing I don't understand is- how come I can't use an equal sign in my syntax?"

minutes and seconds are in the datetime datatype.

====================================================
Regards,
Sean Roussy

Thank you, drive through

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-10 : 13:47:19

A possible option is to use this:

convert(varchar(10), dateadd(wk, -2, GETDATE() ) , 101)

That will return a string of 8/10/2005 which might make it easier to do your date comparisons with.

You could also use DATEDIFF for this:
WHERE DATEDIFF(DD, dbo.weekcommence(date_added), dateadd(wk, -2, GETDATE()) = 0

I believe that the DATEDIFF will ignore the time stamps that SQL puts on the date fields.

Aj

Hey, it compiles.

LoriM
Starting Member

29 Posts

Posted - 2005-08-10 : 16:02:29

Thanks everyone,

Michael, the WeekCommence function is from the post you mentioned, Start of Week Function, from gpl (graham), towards the bottom.

Michael, as a beginner, I'm having a hard time trying to break down what datediff goes with which dateadd, etc. I see from the Start of Week post that 53690 starts from the year 1753, but can you tell me why we are dividing the one dateadd, then multiplying it again?

Here's what my brain computes:
select dateadd(dd,-7,GETDATE()) -- gets me today
select datediff(dd,-53690, dateadd(dd,-7,GETDATE()) ) --92255 weeks ago?
select datediff(dd,-53690, dateadd(dd,-14,GETDATE()) /7)*7 -- = 92255 again. How come?
select dateadd(dd,(datediff(dd,-53690, dateadd(dd,-14,GETDATE()) )/7)*7,-53690)-- = 38565 or 7/25/05

Pretty fancy computing! So is all this counting to avoid the index scanning? Since I don't have date_added indexed, would it be in bad practice (if I ever want to get good at this) to use the WeekCommence function?

You're suggestion gave me the idea to do this, which seems to work, and it's using 'between' dates.

SELECT dbo.weekcommence(date_added)
FROM clients
where dbo.weekcommence(date_added) > dateadd(wk, -2, GETDATE())and
dbo.weekcommence(date_added)< dateadd(wk, -1, GETDATE())

And A.J. I don't get any results with your suggestion until I change around the dd, and wk, which also doesn't make sense.

This gets me the week of 8/8:
WHERE DATEDIFF(wk, dbo.weekcommence(date_added), dateadd(dd, -2, GETDATE())) = 0

It seems to read, get me the difference in weeks from my function(date_added) from this week's Monday minus two weeks.

Do you think using 'wk' is flawed. It doesn't seem to work as expected AT ALL!

Thanks again for your help. I am learning... very slowly...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-10 : 17:07:58

"...can you tell me why we are dividing the one dateadd, then multiplying it again?"
The divide by 7 and then multiply by 7 uses integer arithmetic to round it down to the nearest Monday, and removes the time portion of the date.

"select dateadd(dd,-7,GETDATE()) -- gets me today"
This actually gets you 7 days before today

"select datediff(dd,-53690, dateadd(dd,-7,GETDATE()) ) --92255 weeks ago?"
No, it returns the number of days between Monday, 1753/1/1, the earliest Monday that you can have in a SQL Server datetime, and 7 days before today.

"select datediff(dd,-53690, dateadd(dd,-14,GETDATE()) /7)*7 -- = 92255 again. How come?"
You have a syntax error there, but
select (datediff(dd,-53690, dateadd(dd,-14,GETDATE()))/7)*7
returns 92246, not 92255. It's the number of days between Monday, 1753/1/1 and the Monday on or before 14 days before today.

The algorithim was meant to be fast, and to not depend on the setting of SQL Server datefirst (you can read about that in SQL Server Books Online). In addition, it removes the time portion of the date, something that the dbo.WeekCommence function does not do. The dbo.F_START_OF_WEEK function also lets you select any day as the start of the week.

"So is all this counting to avoid the index scanning? Since I don't have date_added indexed, would it be in bad practice (if I ever want to get good at this) to use the WeekCommence function?"
Another reason to avoid using a function on the table column it that it will have to be called for every row in the table, while in the way I showed you the dbo.F_START_OF_WEEK function is only called twice. This can have a serious performance impact if you have a large table. Even if it is small, why waste CPU time? It is just as simple to program without it. As I already mentioned, the dbo.WeekCommence function does not remove the time, so it makes it a lot harder to use to get the start of your date range.

If you are nervous about dbo.F_START_OF_WEEK because you find it hard to understand, all I can say it that I tested it for every date from 1753/1/1 through 2500/12/31 for ever day of the week (1.536,570 date/start day of week combinations), and it produced correct results each time. Feel free to double check it.

Last, when you select a date range, use this form:
where MyDate >= StartDate and MyDate < EndDate
not
where MyDate > StartDate and MyDate < EndDate
or
where MyDate between StartDate and EndDate
Basically, EndDate is not included in your selection, it is the first point in time you are NOT selecting.

quote:
Originally posted by LoriM

Thanks everyone,

Michael, the WeekCommence function is from the post you mentioned, Start of Week Function, from gpl (graham), towards the bottom.

Michael, as a beginner, I'm having a hard time trying to break down what datediff goes with which dateadd, etc. I see from the Start of Week post that 53690 starts from the year 1753, but can you tell me why we are dividing the one dateadd, then multiplying it again?

Here's what my brain computes:
select dateadd(dd,-7,GETDATE()) -- gets me today
select datediff(dd,-53690, dateadd(dd,-7,GETDATE()) ) --92255 weeks ago?
select datediff(dd,-53690, dateadd(dd,-14,GETDATE()) /7)*7 -- = 92255 again. How come?
select dateadd(dd,(datediff(dd,-53690, dateadd(dd,-14,GETDATE()) )/7)*7,-53690)-- = 38565 or 7/25/05

Pretty fancy computing! So is all this counting to avoid the index scanning? Since I don't have date_added indexed, would it be in bad practice (if I ever want to get good at this) to use the WeekCommence function?

You're suggestion gave me the idea to do this, which seems to work, and it's using 'between' dates.

SELECT dbo.weekcommence(date_added)
FROM clients
where dbo.weekcommence(date_added) > dateadd(wk, -2, GETDATE())and
dbo.weekcommence(date_added)< dateadd(wk, -1, GETDATE())

And A.J. I don't get any results with your suggestion until I change around the dd, and wk, which also doesn't make sense.

This gets me the week of 8/8:
WHERE DATEDIFF(wk, dbo.weekcommence(date_added), dateadd(dd, -2, GETDATE())) = 0

It seems to read, get me the difference in weeks from my function(date_added) from this week's Monday minus two weeks.

Do you think using 'wk' is flawed. It doesn't seem to work as expected AT ALL!

Thanks again for your help. I am learning... very slowly...

CODO ERGO SUM

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-10 : 17:16:54

I'm sorry, I should of quoted what I was referring to:

"One thing I don't understand is- how come I can't use an equal sign in my syntax?"

The DATEDIFF function would help you get around the whole Equal sign thingy. Sorry for not being clear on that.

Aj

Hey, it compiles.

LoriM
Starting Member

29 Posts

Posted - 2005-08-10 : 18:26:00

Whew! Thanks for explaining! I'll work on using the f_Start_of_Week function to better understand it and datetime functions.

Thanks again for your patience.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-11 : 09:28:34

I have been writing SQL for 5 years and still hang myself from time to time with DATEDIFF. I get the logic on the start date and end date mixed up. But I think that once you get comfortable with it, you will prefer to write it instead of other methods.

Aj

Hey, it compiles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources