t-sql first last day date of the month
-
Comments posted to this topic are about the item t-sql first last day date of the month
-
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?
-
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 datetime
set @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)) - 1
Cheers, R.
-
For the first day of the month, why use the double negative -- why not just
dateadd(day,1-datepart(day,@d),@d)
-
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) - @FLArkware
-
The other question is why use more variables and code than needed?
DECLARE @d SMALLDATETIME
SELECT 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
-
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
-
The code in my previous post will also strip off the time
-
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 month
SELECT
dateadd(day,-(datepart(day,@d)),dateadd(mm,1,@d)),
-- Returns 2009-01-30 00:00:00.000, incorrect
dateadd(day,-(datepart(day,dateadd(mm,1,@d))),dateadd(mm,1,@d));
-- Returns 2009-01-31 00:00:00.000, correct
-
Thanks for the script.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply