Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

t-sql first last day date of the month Expand / Collapse
Author
Message
Posted Thursday, November 12, 2009 12:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 3, 2014 7:09 AM
Points: 313, Visits: 178
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

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, December 4, 2011 9:20 PM
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?
Post #826569
Posted Tuesday, December 1, 2009 5:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:35 AM
Points: 28, Visits: 60
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.
Post #826573
Posted Tuesday, December 1, 2009 6:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 5:43 AM
Points: 37, Visits: 664
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:06 PM
Points: 44, Visits: 1,688
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
Post #826628
Posted Tuesday, December 1, 2009 1:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 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 ;)
Post #826979
Posted Wednesday, December 2, 2009 3:31 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, October 26, 2014 7:16 PM
Points: 634, Visits: 810

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:53 AM
Points: 33, Visits: 117
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
Post #827696
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse