Determin start and end date of a month

Hi all,
does anyone know of a way of dynamically determining the start and end date
of a month.
i.e. if i pass 15th March as a parameter, I can determine that the start
date is 1st March (which is obvious) and the end date is 31st March -- this
is the key part!
Many thanks
ImmyImmy,
Hints:
You know what is the 1st day of the NEXT month, too.
So you date (add) minus 1 day...
HTH,
Robert.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OBf1GoFRGHA.252@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> does anyone know of a way of dynamically determining the start and end
> date of a month.
> i.e. if i pass 15th March as a parameter, I can determine that the start
> date is 1st March (which is obvious) and the end date is 31st March --
> this is the key part!
> Many thanks
> Immy
>|||Rob... Yes, this I know... but it doesnt get me around the problem.
Basically, I am creating a procedure that will add information into a table
about 'A' given day... they day could be any day and will run daily.
Thus I need to be able to determine that the current day is say... 21st Feb
and that it's end date is 28th/289th etc.. so the trick is that come the 1st
of March, it will have different end dates to Feb... and then of course leap
years... etc...
"news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
> Immy,
> Hints:
> You know what is the 1st day of the NEXT month, too.
> So you date (add) minus 1 day...
> HTH,
> Robert.
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OBf1GoFRGHA.252@.TK2MSFTNGP10.phx.gbl...
>|||A calendar table would help here and with all sorts of Date problems.
http://www.aspfaq.com/show.asp?id=2519
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Rob... Yes, this I know... but it doesnt get me around the problem.
> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
> Thus I need to be able to determine that the current day is say... 21st
> Feb and that it's end date is 28th/289th etc.. so the trick is that come
> the 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
>
> "news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
> news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
>|||THATS the BAD BOY!!!
Actually - I am creating a calendar system.. hence the question. Many thanks
Immy
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:OXCVv%23FRGHA.1576@.tk2msftngp13.phx.gbl...
>A calendar table would help here and with all sorts of Date problems.
> http://www.aspfaq.com/show.asp?id=2519
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||Immy:

> Rob... Yes, this I know... but it doesnt get me around the problem.
Perhaps I'm misunderstanding.

> and that it's end date is 28th/289th etc.. so the trick is that come the
> 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
Using the technique I have already hinted at, the intricacies of the
calendar (including leap years) would all be handled by the server.

> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
OK, here is an example for you:
--DROP TABLE #Test
GO
CREATE TABLE #Test (SomeDay DATETIME NOT NULL)
GO
SET DATEFORMAT YMD
INSERT #Test (SomeDay) VALUES ('2005-02-01')
INSERT #Test (SomeDay) VALUES ('2004-02-01')
INSERT #Test (SomeDay) VALUES ('2003-02-01')
INSERT #Test (SomeDay) VALUES ('2002-02-01')
INSERT #Test (SomeDay) VALUES ('2001-02-01')
INSERT #Test (SomeDay) VALUES ('2000-02-01')
INSERT #Test (SomeDay) VALUES ('1999-02-01')
INSERT #Test (SomeDay) VALUES ('1999-03-02')
INSERT #Test (SomeDay) VALUES ('1999-04-03')
INSERT #Test (SomeDay) VALUES ('1999-05-04')
INSERT #Test (SomeDay) VALUES ('1999-06-05')
INSERT #Test (SomeDay) VALUES ('1999-08-31')
GO
SET DATEFORMAT YMD
SELECT SomeDay,
LastDayOfMonthOfSomeDay = DATEADD(d, -1,
(/* This expression is the FIRST day of the FOLLOWING month */
CONVERT(DATETIME, CAST(YEAR(DATEADD(m, 1, SomeDay)) AS CHAR(4)) + '/'
+ CAST(MONTH(DATEADD(m, 1, SomeDay)) AS VARCHAR(2)) + '/1')
/**/)
)
FROM
#Test
Robert
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
> Thus I need to be able to determine that the current day is say... 21st
> Feb and that it's end date is 28th/289th etc.. so the trick is that come
> the 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
>
> "news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
> news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
>|||Glad I could help.
I'm not sure that I like being called a BAD BOY in this newsgroup though.
:-)
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OPw9wDGRGHA.1576@.tk2msftngp13.phx.gbl...
> THATS the BAD BOY!!!
> Actually - I am creating a calendar system.. hence the question. Many
> thanks
> Immy
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:OXCVv%23FRGHA.1576@.tk2msftngp13.phx.gbl...|||Was referring to the solution.. not u! ;)
Cheers
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23t5IcIGRGHA.336@.TK2MSFTNGP12.phx.gbl...
> Glad I could help.
> I'm not sure that I like being called a BAD BOY in this newsgroup though.
> :-)
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OPw9wDGRGHA.1576@.tk2msftngp13.phx.gbl...
>|||On Fri, 10 Mar 2006 16:09:19 -0000, Immy wrote:

>Rob... Yes, this I know... but it doesnt get me around the problem.
>Basically, I am creating a procedure that will add information into a table
>about 'A' given day... they day could be any day and will run daily.
>Thus I need to be able to determine that the current day is say... 21st Feb
>and that it's end date is 28th/289th etc.. so the trick is that come the 1s
t
>of March, it will have different end dates to Feb... and then of course lea
p
>years... etc...
Hi Immy,
Here's yet a third way to do this (using the test data kindly provided
by Robert):
SELECT SomeDay,
DATEADD(month,
DATEDIFF(month, '20050101', SomeDay),
'20050131') AS LastDayOfMonthOfSomeDay
FROM #Test
The trick here is to calculate the number of full months between your
date and the first month of a fixed starting point, then add that number
to the last day of the same month. You are free to choose any date
within the range of allowed datetimes as starting point, but make sure
to pick a month with 31 days!
Hugo Kornelis, SQL Server MVP