This script showed up in my daily message from SQLServerCentral.com. Stephen,
did you pinch this from there? :)
Watch out for word wrapping. One of the nice things about t-sql is you can use
inline comments and that definitely got a bit fugly looking in my mail client.
Here's the full posting from this little tip.
/* =============================================================*/
/* Find number of days in month for a selected date */
/* By Mariana Maas */
/*--------------------------------------------------------------*/
/* Method:
*/
/* - Create the date with the first day of the selected date */
/* - Add a month using the DATEADD function */
/* => now you have the first day of the next month */
/* - Subtract 1 day using the DATEADD function */
/* => now you have the last day of the current month */
/* - select the day and wallah!
*/
/*==============================================================*/
-->> The following code has been broken down into steps with print statements
in between, so the logic behind it can be shown, it can all go into one
statement (see bottom)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @mydate DATETIME
,@workStr DATETIME
SET @mydate = '2008-02-24'
--> find first day of month
SET @workStr = CAST( CAST(YEAR(@mydate) AS VARCHAR(04)) + RIGHT( ('0' +
CAST(MONTH(@mydate) AS VARCHAR(02))) , 2 ) + '01' AS DATETIME)
PRINT @workStr
--> Add a month to find first day of following month, then subtract a day to
get last day of current month, extract the day
SELECT DAY( DATEADD ( DAY, -1, DATEADD(MONTH,1,@workStr)))
-->> Code in one statement:
---------------------------
SELECT DAY( DATEADD --<< 4. Extract the last day
( DAY, -1, --<< 3. Find last day
of current month
DATEADD(MONTH,1, --<< 2. Find first day of next month
CAST( CAST( --<< 1. Find first day of the
current month
YEAR(CURRENT_TIMESTAMP) AS VARCHAR(04))
+ RIGHT( ('0' + CAST(MONTH(CURRENT_TIMESTAMP) AS VARCHAR(02))) , 2 )
+ '01' AS DATETIME)
)
)
)
--
rk
-----Original Message-----
From: [email protected] [mailto:[email protected]] On
Behalf Of MB Software Solutions, LLC
Sent: Tuesday, May 10, 2011 9:01 AM
To: [email protected]
Subject: Re: [NF] T-SQL Last day of the month
On 5/10/2011 8:46 AM, Stephen Russell wrote:
> Start is now in the statement and builds up through the iterations of
> the date math.
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD0441924151565@ACKBWDDQH1.artfact.local
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
