Date Help with Stored Procedure

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)

Date Help with Stored Procedure

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-08 : 13:20:50

I have a stored procedure called from within an Excel file. This Excel file is viewed daily. Within this SP there is a hard coded date reference from within the where clause (See below).

Every month, this SP needs to be modified to include the current months date span in the where clause(See below).

It would be ideal to have this SP only look at the current month and all dates within this month.

Any thoughts, hope this makes sense?

Thanks

ALTER procedure [dbo].[stp_test]
AS
SELECT 'CoSym' = ltrim(lh.CoSym),
SourceType,
FormNum,
RptDate = (case when Date2 = '' then RptDate else Date2 end),
TranType,
'ItemSum' = sum(Items),
LastUpdate
FROM Test_History lh
WHERE ((lastupdate > '01/01/2009' and lastupdate < '02/02/2009'))
group by lh.CoSym, SourceType, FormNum, RptDate, TranType, LastUpdate, Date2
order by lh.CoSym, LastUpdate

drewex
Starting Member

4 Posts

Posted - 2009-01-08 : 13:23:42

try this it will get everything this month and year
WHERE month(lastupdate) = month(getdate()) and year(lastupdate) = year(getdate())

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-08 : 13:26:40

this should do it:


where  lastupdate >= dateadd(month, datediff(month, 0, getdate()), 0)
and    lastupdate <  dateadd(month, datediff(month, 0, getdate()), 1)

Be One with the Optimizer
TG

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-08 : 13:30:26

Thanks for the reply, what I am looking for is this.....

Lets say today is January 15th, 2009. I want my where clause to include all records between January 1st, 2009 - January 31st, 2009.

Fast forward to next month.... Upon Feb 1st, the date within the where clause will dynamically change to Feb 1st, 2009 - Feb 28, 2009 and so on.....

Basically, I want to eliminate having to hard code the current month in my where clause every month.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-08 : 13:31:18

TG, I think you got it.... Will give it a try.

drewex
Starting Member

4 Posts

Posted - 2009-01-08 : 13:32:39

just realized the 2/2/2009 if your looking for something like today is = 1/8/2009 and data from 1/8/2009 to 2/8/2009
then

WHERE lastupdate between getdate() and dateadd(month, 1, getdate())
this is time specific so if you need just date specific u should use

Cast(Convert(varchar(10),getdate(), 101) AS Datetime) instead getdate(), it will give you only date part of today

drewex
Starting Member

4 Posts

Posted - 2009-01-08 : 13:33:54

Hehehe while i was writing you responded. Yea for your q the first code will work.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-08 : 13:59:26

In case you were interested, this is what I was looking for.....

SELECT 'CoSym' = ltrim(lh.CoSym),
SourceType,
FormNum,
RptDate = (case when Date2 = '' then RptDate else Date2 end),
TranType,
'ItemSum' = sum(Items),
LastUpdate

FROM Test_History lh
WHERE
lastupdate > DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) and
lastupdate <= dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
group by lh.CoSym, SourceType, FormNum, RptDate, TranType, LastUpdate, Date2
order by lh.CoSym, LastUpdate

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-08 : 14:08:55

why 3ms? you're missing records between lastupdate timestamp of 23:59:59 998 and 00:00:00 000 at the end of every month

what TG suggested is perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources