SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Bernabe Diaz / t-sql first last day date of the month / Latest Posts

RE: t-sql first last day date of the month

benkoskysa — Wed, 02 Dec 2009 11:14:34 GMT

These return different results for the end of months preceding a month that has more days in it. For instance January has 31 days, and February only 28 or 29 days.Here is an example:DECLARE @d datetime;SET @d = '01/29/2009';-- Last day of monthSELECTdateadd(day,-(datepart(day,@d)),dateadd(mm,1,@d)), -- Returns 2009-01-30 00:00:00.000, incorrectdateadd(day,-(datepart(day,dateadd(mm,1,@d))),dateadd(mm,1,@d)); -- Returns 2009-01-31 00:00:00.000, correct

RE: t-sql first last day date of the month

Jeff Jordan — Wed, 02 Dec 2009 06:01:33 GMT

The code in my previous post will also strip off the time ;)

RE: t-sql first last day date of the month

frfernan — Wed, 02 Dec 2009 03:31:26 GMT

Hello,if you want to take off hours, minutes and seconds, getting only the "date" part of the calculated date, add this line of code: SET @R = cast(floor(cast(@R as FLOAT)) as DATETIME)Best wishes, Francesc

RE: t-sql first last day date of the month

Jeff Jordan — Tue, 01 Dec 2009 13:10:15 GMT

The other question is why use more variables and code than needed? DECLARE @d SMALLDATETIMESELECT DATEADD(mm, DATEDIFF(mm, 0, ISNULL(@d, GETDATE())), 0)SELECT DATEADD(d, -1, DATEADD(mm, 1 + DATEDIFF(mm, 0, ISNULL(@d, GETDATE())), 0))As a side note, if you're going to do quite a bit of date manipulation you're better off using a calendar table. There are plenty of examples out there so I won't go into all of that here. What if you need the first and last date of every month in a period? You could use a numbers table and date functions but the calendar table is much more efficient.Just my two cents worth ;)

RE: t-sql first last day date of the month

ArkWare — Tue, 01 Dec 2009 06:54:31 GMT

So many ways to calculate dates.... :-)within the context of the SP, how about only one select statement to give you both first and last?need to change @FL to 0 = First day and 1 = Last day[code]DECLARE @FL INT, @d AS DATETIMESET @d = ISNULL(@d,GETDATE())SET @FL = 0 -- First day of the monthSELECT DATEADD(mm, @FL + DATEDIFF(mm, 0, @d), 0) - @FLSET @FL = 1 -- Last day of the monthSELECT DATEADD(mm, @FL + DATEDIFF(mm, 0, @d), 0) - @FL[/code]Arkware

RE: t-sql first last day date of the month

brian.neumeier-603907 — Tue, 01 Dec 2009 06:09:35 GMT

For the first day of the month, why use the double negative -- why not just dateadd(day,1-datepart(day,@d),@d)

RE: t-sql first last day date of the month

richard.jereb — Tue, 01 Dec 2009 05:30:33 GMT

A possible alternative is convert/cast with datetime styles (e.g. 112 for ISO in the form of yyyymmdd). This comes in often very handy for date calculations:declare @MyDate datetimeset @MyDate = getdate()-- first day of given month (yyyymm01)select convert(datetime, convert(varchar, (year(@MyDate) * 10000) + (month(@MyDate) * 100) + 1), 112)-- last day of given month (first day of next minus one day -> yyyymm01 + 1 month - 1 day)select dateadd(month, 1, convert(datetime, convert(varchar, (year(@MyDate) * 10000) + (month(@MyDate) * 100) + 1), 112)) - 1Cheers, R.

RE: t-sql first last day date of the month

malu.mn.ktr — Tue, 01 Dec 2009 05:20:55 GMT

Hi,I think the Last date can be font by SELECT @R=dateadd(day,-(datepart(day,@d)),dateadd(mm,1,@d))No need to use SELECT @R=dateadd(day,-(datepart(day,dateadd(mm,1,@d))),dateadd(mm,1,@d))Because, datepart(day,@d) and datepart(day,dateadd(mm,1,@d)) will return the same result. Then why to add one month to @d?

t-sql first last day date of the month

diaz.bernabe — Thu, 12 Nov 2009 12:28:11 GMT

Comments posted to this topic are about the item [B]t-sql first last day date of the month[/B]