Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 t-sql first last day date of the month Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, November 12, 2009 12:28 PM
 Old Hand Group: General Forum Members Last Login: Tuesday, December 15, 2015 8:06 AM Points: 313, Visits: 194
 Comments posted to this topic are about the item t-sql first last day date of the month
Post #818046
 Posted Tuesday, December 1, 2009 5:20 AM
 Valued Member Group: General Forum Members Last Login: Sunday, December 4, 2011 9:20 PM Points: 62, Visits: 23
Post #826569
 Posted Tuesday, December 1, 2009 5:30 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, December 9, 2015 6:18 AM Points: 28, Visits: 62
 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.
Post #826573
 Posted Tuesday, December 1, 2009 6:09 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, January 11, 2016 2:32 PM Points: 38, Visits: 717
 For the first day of the month, why use the double negative -- why not just dateadd(day,1-datepart(day,@d),@d)
Post #826592
 Posted Tuesday, December 1, 2009 6:54 AM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, March 22, 2016 5:39 AM Points: 45, Visits: 1,738
 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`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`Arkware
Post #826628
 Posted Tuesday, December 1, 2009 1:10 PM
 Grasshopper Group: General Forum Members Last Login: Friday, February 5, 2010 6:28 AM Points: 24, Visits: 52
 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 ;)
Post #826979
 Posted Wednesday, December 2, 2009 3:31 AM
 Say Hey Kid Group: General Forum Members Last Login: Monday, May 23, 2016 7:31 AM Points: 677, Visits: 812
 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
Post #827293
 Posted Wednesday, December 2, 2009 6:01 AM
 Grasshopper Group: General Forum Members Last Login: Friday, February 5, 2010 6:28 AM Points: 24, Visits: 52
 The code in my previous post will also strip off the time ;)
Post #827371
 Posted Wednesday, December 2, 2009 11:14 AM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, September 8, 2015 7:35 AM Points: 33, Visits: 125
 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
Post #827696
 Posted Monday, May 23, 2016 7:02 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 8, 2016 6:45 AM Points: 8,328, Visits: 858
 Thanks for the script.
Post #1788845

 Permissions