first of the month to a given date? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

first of the month to a given date?

Author

Topic

genic
Yak Posting Veteran

57 Posts

Posted - 2003-03-19 : 12:46:45

table
-----
prjReqID
prjReqDate

i need to return all prjReqID's from the first of the given dates month. if the given date is 3/18/03 then i need to find all records from 3/1/03 to 3/18/03 based on the prjReqDate field. the datatype of that field is a smalldatetime. anyone have any suggestions or sample code? i searched, but i didnt dig that deep. :(

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2003-03-19 : 12:58:18

manipulating the date a little allows you to get the start of the month and the end of the month.


CREATE PROCEDURE sp_SelectRecordsForMonth(
@theDate smalldatetime
)

as

DECLARE @firstDayOfMonth smalldatetime
DECLARE @lastDayOfMonth smalldatetime

SELECT @firstDayOfMonth = DateAdd(dd, -(Day(@theDate) - 1), @date)

SELECT @lastDayOfMonth = DateAdd(dd, -1, DateAdd(mm, 1, @firstDayOfMonth))

SELECT prjReqID, prjReqDate FROM theTable WHERE prjReqDate BETWEEN @firstDayOfMonth and @lastDayOfMonth

GO

Got SQL?

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2003-03-19 : 12:59:53

If you really wanted to do it in one line you could do this as well:


SELECT prjReqID, prjReqDate FROM theTable WHERE prjReqDate BETWEEN DateAdd(dd, -(Day(@date) - 1), @date) and DateAdd(dd, -1, DateAdd(mm, 1, DateAdd(dd, -(Day(@date) - 1), @date)))

Got SQL?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources