SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


t-sql first last day date of the month


t-sql first last day date of the month

Author
Message
diaz.bernabe
diaz.bernabe
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 194
Comments posted to this topic are about the item t-sql first last day date of the month
malu.mn.ktr
malu.mn.ktr
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 23
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?
richard.jereb
richard.jereb
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 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 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.
brian.neumeier-603907
brian.neumeier-603907
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 743
For the first day of the month, why use the double negative -- why not just

dateadd(day,1-datepart(day,@d),@d)
ArkWare
ArkWare
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 1741
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 DATETIME
SET @d = ISNULL(@d,GETDATE())
SET @FL = 0 -- First day of the month
SELECT DATEADD(mm, @FL + DATEDIFF(mm, 0, @d), 0) - @FL
SET @FL = 1 -- Last day of the month
SELECT DATEADD(mm, @FL + DATEDIFF(mm, 0, @d), 0) - @FL





Arkware
Jeff Jordan
Jeff Jordan
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 52
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 Wink
gofrancesc
gofrancesc
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2349 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
Jeff Jordan
Jeff Jordan
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 52
The code in my previous post will also strip off the time Wink
benkoskysa
benkoskysa
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 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 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
Iwas Bornready
Iwas Bornready
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49880 Visits: 886
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search