Click here to monitor SSC
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
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 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
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
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 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 Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

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

dateadd(day,1-datepart(day,@d),@d)
ArkWare
ArkWare
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 1739
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
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 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
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 52
The code in my previous post will also strip off the time Wink
benkoskysa
benkoskysa
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8648 Visits: 885
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