Operations with dates - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Operations with dates

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-30 : 11:35:49

Hi,

I have never made operations with date format, and now I have to filter orders by dates.

Here is my challenge:

I receive the parameter @last_orders from app which its value can be 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10.

If its value is 1 I have to return records where order_date (smalldatetime column) is equal to current date (day/month/year).
If its value is 2 I have to return records where order_date is equal to the last two days.
If its value is 3 I have to return records where order_date is equal to the last three days.
If its value is 4 I have to return records where order_date is equal to the last week.
If its value is 5 I have to return records where order_date is equal to the last two weeks.
If its value is 6 I have to return records where order_date is equal to the last month.
If its value is 7 I have to return records where order_date is equal to the last two months.
If its value is 8 I have to return records where order_date is equal to the last three months.
If its value is 9 I have to return records where order_date is equal to the last six months.
If its value is 10 I have to return records where order_date is equal to the last year.

How can I achieve it?

Perhaps something like this:


WHERE  IsNull(order_date, 0) = CASE WHEN IsNull(@last_orders, 0) = 0 Then IsNull(order_date, 0) 
       WHEN IsNull(@last_orders, 0) = 1 Then GETDATE()
       WHEN IsNull(@last_orders, 0) = 2 Then ?¿
        ... End

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-30 : 11:53:15

This should work:

where
	order_date >=
	case @last_orders
	-- Orders today 
	when 1 then DateAdd(day,DateDiff(day,0,getdate()),0)
	-- Orders last 2 days
	when 2 then DateAdd(day,DateDiff(day,0,getdate())-1,0)
	-- Orders last 3 days
	when 3 then DateAdd(day,DateDiff(day,0,getdate())-2,0)
	-- Orders in last week
	when 4 then DateAdd(day,DateDiff(day,0,getdate())-6,0)
	-- Orders in last 2 week
	when 5 then DateAdd(day,DateDiff(day,0,getdate())-13,0)
	-- Orders this month
	when 6 then DateAdd(month,DateDiff(month,0,getdate()),0)
	-- Orders last 2 months
	when 7 then DateAdd(month,DateDiff(month,0,getdate())-1,0)
	-- Orders last 3 months
	when 8 then DateAdd(month,DateDiff(month,0,getdate())-2,0)
	-- Orders last 6 months
	when 9 then DateAdd(month,DateDiff(month,0,getdate())-5,0)
	-- Orders last 12 months
	when 10 then DateAdd(month,DateDiff(month,0,getdate())-11,0)
	-- else any date
	else convert(datetime,'1753/1/1')
	end

CODO ERGO SUM

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-30 : 11:57:16

[code]where order_date > dateAdd(day, - @last_orders, dateAdd(day, - @last_orders, DATEADD(d,DATEDIFF(d,0,GETDATE()),0)))[/code]Is it what you are looking for? For explanation read: [url]http://www.sql-server-performance.com/fk_datetime.asp[/url]

[Edited] Sorry I didn't read more then first 3 ifs.

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-30 : 12:49:57

Now I have to go, I am going to see the post tomorrow to continue working on it

Thank you

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-30 : 18:01:25

@Last_orders parameter is actually list index (or something like that) passed directly to stored procedure, so stored procedure now has to decode it. If in future is requested to add 4 days option or 10 days or something like that, client code will have to be changed and stored procedure implementation as well.

Better decode date interval on the client and pass number of days as stored procedure parameter.

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-03 : 04:00:19

Hi mmarovic,

I am trying to implement more or less what you said. I pass the number of days to SP to find where order_date column is between today and specified last days:
[CODE]
WHERE order_date <= dateadd(day, 0, GETDATE()) And order_date > dateadd(day, -@last_orders, order_date)
[/CODE]

But it doesn' t work. I don' t understand why, it seems logic.. What is wrong?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-03 : 04:30:28

WHERE order_date <= dateadd(day, 0, GETDATE()) And order_date > dateadd(day, 0 - @last_orders , order_date)

Duane.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-03 : 05:03:29

Hi cesark, sorry I made an mistake, the correct query uses approach already posted by Michael:

WHERE order_date <= DateAdd(day,DateDiff(day,0,getdate()),0) And order_date > dateadd(day, -@last_orders, DateAdd(day,DateDiff(day,0,getdate()),0))

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-03 : 05:36:19

Yes! now it works

I am reading my Transact-SQL help to try to understand the code, because for me this should work:

WHERE order_date <= GETDATE() And order_date > dateadd(day, -@last_orders, order_date)

And I don' t understand what does the code in bold:

WHERE order_date <= DateAdd(day,DateDiff(day,0,getdate()),0) And order_date > dateadd(day, -@last_orders, DateAdd(day,DateDiff(day,0,getdate()),0))

DateAdd simply returns a date calculated with 3 parameters (datepart , number, date), datepart is the part of the date we want to make the operation, number is the number we want to add or reduce, and date is the date where we want to make the operation.

And I understand that DateDiff only retruns an interval between two dates.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-03 : 05:39:05

GetDate() has Date and Time
But DateAdd(day,DateDiff(day,0,getdate()),0) will have only Date Part

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-03 : 05:51:08

See:

http://www.sql-server-performance.com/fk_datetime.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-03 : 05:55:20

quote:
Originally posted by mmarovic

See:
http://www.sql-server-performance.com/fk_datetime.asp

One of my favorite Links

Madhivanan

Failing to plan is Planning to fail

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-03 : 06:24:05

Ok thank you, I am going to read it

Now I am trying to handle the case (@last_orders = 0), I mean when the user hasn' t specified any period of time. In this case I want no filter is made in the order_dates column. I tried this:


@last_orders = CASE WHEN @last_orders = 0 Then order_date Else order_date <= DateAdd(day,DateDiff(day,0,getdate()),0) And order_date > dateadd(day, -@last_orders, DateAdd(day,DateDiff(day,0,getdate()),0)) End

But it doesn' t work. It must be a way..

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-03 : 06:27:57

I would rather call separate stored procedure or (less preferable) pass 7000 or something like that as @last_orders value.

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-03 : 06:42:59

I would prefer a way to not filter this column in that case (@last_orders = 0), as I have into my 'Where' clause in other filter columns.

For example:


WHERE ... And ... And ... And ...
And IsNull(Product_num, 0) = CASE WHEN IsNull(@Product, 0) = 0 Then IsNull(Product_num, 0) Else @Product End

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-03 : 07:58:57

That way you again tie your sp code to the "business" logic that is (and should stay) somewhere else.
Sp should be db api and business logic should be elsewhere. Your db method (stored procedure) should be about returning specific data for specified time period.
Client should use that method according to the logic implemented there (on the client/middle tier).

There are also peformance reasons not to do like you want (building execution plans, sp (re)compilations and parameter sniffing).

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-03 : 09:47:30

Well, in that case I will have to use the solution you pointed to. Do you meant call another SP from my current SP? If so, what it have to do this another SP? I don' t understand very well the idea. Would you mind giving a simple example please?

The other idea, but worse, is If I have understood it well, instead of put '0' value on client app for none selection, I have to put '7000' value (meaning 7000 days) or similar.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-03 : 10:09:15

No, I wouldn't call another stored procedure from one we are talking about. What I prefer is either:

1. Have one stored procedure that queries for time period and another one that selects regardless of time. Which one should be executed will be implemented on the client/middle tier; or
2. Have just one procedure selecting based on period in days and in case user wants all data regardless of period then pass a huge number as a @last_orders value. This is not actually different then decoding that choice number 4 is one week period.

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-03 : 11:01:26

I see, now it' s much more clear. Thank you so much mmarovic!

I already read the article you recommended me (http://www.sql-server-performance.com/fk_datetime.asp). And now I understand why we have to use DateAdd(day,DateDiff(day,0,getdate()),0) instead of as I said GETDATE() to add or subtract days from 'today'. And I see it' s because 'getdate()' returns date and time, so isn' t exact, instead with DateDiff(day,0,getdate()) we get the date and time but set exactly to midnight, so the calculations will be exact.

It' s a very good article

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-03 : 11:07:42

You are wellcome!

Article author is posting on this forum too, his handle is FrankKalis (or something like that).

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-03 : 13:44:01

Since you never commented on it, I assume that you didn't even try the code I posted.

I believe it was an exact solution to your problem the way it was stated.

quote:
Originally posted by cesark

I see, now it' s much more clear. Thank you so much mmarovic!

I already read the article you recommended me (http://www.sql-server-performance.com/fk_datetime.asp). And now I understand why we have to use DateAdd(day,DateDiff(day,0,getdate()),0) instead of as I said GETDATE() to add or subtract days from 'today'. And I see it' s because 'getdate()' returns date and time, so isn' t exact, instead with DateDiff(day,0,getdate()) we get the date and time but set exactly to midnight, so the calculations will be exact.

It' s a very good article

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources