Days and Months - 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 2005 Forums

Transact-SQL (2005)

Days and Months

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-05-02 : 06:08:05

Hello,

I need to retrieve various records from a database based on UpdatedTime.

I am providing 3 variables:
1. A DateTime. Can be the current on one in the past.
2. A Mode (Month or Day)
3. A Period.

What I need is to get all the records which Updated Date is inside the period counting backward from the DateTime provided.

Example 1:
DateTime = 20.02.2007; Mode = Month; Period = 4

I should get all records which updated date is in month 02.2007,
01.2007, 12.2006 or 11.2006 (4 months including the month of the
DateTime provided)

Example 2:
DateTime = 20.02.2007; Mode = Day; Period = 3

I should get all records which updated date is in days 20.02.2007,
19.02.2007, 18.02.2007 (3 days including the day of the DateTime
provided)

How can I do this?
Am I doing the right thing by providing this 3 parameters?

Thank You,
Miguel

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-02 : 06:18:04

if u want day, month, year enter day, month and year as first argument in the follwing function...

dateadd(day,-2, getdate())

- value reduces the corresponding part...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 06:21:43

You have two ways:

IF @mode = 'month'
   Select ...
   From ...
   Where
     month(datecol) between month(@date) and month(@date) - @period +1
Else IF @mode = 'day'
....

Or using CASE in the WHERE clause.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-05-02 : 08:42:23

The problem with the month() and day() function is that it ignores the year break. If he passes january and wants a period of 4 months, it would fail. Try something like


DECLARE @endDate datetime
SET @endDate = dateadd(d, datediff(d, 0, @datePassed), 0) -- strips time (sets to midnight)
SELECT ...
FROM   ...
WHERE  dateCol BETWEEEN CASE
            WHEN @mode = 'month' THEN dateadd(m, -@period, @endDate)
            ELSE dateadd(d, -@period, @endDate)
            END
       AND dateadd(d, 1, @endDate) -- want to include all of @passedDate up to midnight of the next day

/jeff

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-05-02 : 08:51:46

"Am I doing the right thing by providing this 3 parameters?"

Depends on your application. If your application already has the date range (start - end), then pass those. If your application already has the mode and period, then it's fine to let sql do the date calculations.

/jeff

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-05-02 : 09:10:56

I'm half-right.

Using month() and day() ignores the year. Which would fail across year breaks, yes, but it would also include all matches of that month no matter the year.

Also, you don't want to use BETWEEN as I showed because it would include "midnight of the next day".

So do


WHERE  dateCol >= CASE
            WHEN @mode = 'month' THEN dateadd(m, -@period, @endDate)
            ELSE dateadd(d, -@period, @endDate)
            END
       AND dateCol < dateadd(d, 1, @endDate)

/jeff

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-05-07 : 08:01:33

Hi,

Would it be possible, inside the CASE, to add a new option which would select the last N records since datePassed?

DECLARE @endDate datetime
SET @endDate = dateadd(d, datediff(d, 0, @datePassed), 0) -- strips time (sets to midnight)
SELECT ...
FROM ...
WHERE dateCol >= CASE
WHEN @mode = 'month' THEN
dateadd(m, -@period, @endDate)
WHEN @mode = 'day' THEN
dateadd(d, -@period, @endDate)
WHEN @mode = 'count' THEN

GET @N records counting backward from DatePassed
This means if @N = 20 I would get 20 records from DatePassed.
Consider the date passed is 20.01.2007 and in that day I would have 10 records.
Then I would get those 10 records and would go back, 19.01, 18.01, 17.01, ... until I get a total of 20 records.

END
AND dateCol < dateadd(d, 1, @endDate)

Thanks,
Miguel

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 08:49:59

declare @mode varchar(20), @period int, @enddate datetime, @n int
Set @mode = 'count'
Set @period = 60
Set @enddate = Getdate()
Set @n =10
SELECT *
FROM
(Select * , Num =
(Select count(distinct(last_mod_dt)) From fini_deal_buyer
where last_mod_dt >= a.last_mod_dt) from fini_deal_buyer a)
t
WHERE last_mod_dt >= CASE
WHEN @mode = 'month' THEN
dateadd(m, -@period, @endDate)
WHEN @mode = 'day' Or @mode = 'count' THEN
dateadd(d, -@period, @endDate)
End and (num >= case when @mode <> 'count' then 0 end or num <= case when @mode = 'count' then @n end -- see how it works and see how to optimize it)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources