t-sql first last day date of the month

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Bernabe Diaz » t-sql first last day date of the month

t-sql first last day date of the month

Rate Topic

Display Mode

Topic Options

Author

Message

diaz.bernabe

Posted Thursday, November 12, 2009 12:28 PM

Old Hand

Group: General Forum Members
Last Login: Friday, May 10, 2013 8:48 AM
Points: 313, Visits: 165

Comments posted to this topic are about the item t-sql first last day date of the month

Post #818046

malu.mn.ktr

Posted Tuesday, December 01, 2009 5:20 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, December 04, 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

richard.jereb

Posted Tuesday, December 01, 2009 5:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 22, 2013 2:53 AM
Points: 28, Visits: 47

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

brian.neumeier-603907

Posted Tuesday, December 01, 2009 6:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 10:51 AM
Points: 37, Visits: 586

For the first day of the month, why use the double negative -- why not just

dateadd(day,1-datepart(day,@d),@d)

Post #826592

ArkWare

Posted Tuesday, December 01, 2009 6:54 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:32 AM
Points: 40, Visits: 1,531

So many ways to calculate dates.... Smile

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

Jeff Jordan

Posted Tuesday, December 01, 2009 1:10 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 05, 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

frfernan

Posted Wednesday, December 02, 2009 3:31 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:57 AM
Points: 617, Visits: 797

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

Jeff Jordan

Posted Wednesday, December 02, 2009 6:01 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 05, 2010 6:28 AM
Points: 24, Visits: 52

The code in my previous post will also strip off the time ;)

Post #827371

benkoskysa

Posted Wednesday, December 02, 2009 11:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 04, 2013 6:44 AM
Points: 31, Visits: 101

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 »

Permissions