How to Find Beginning and End of Month depending on YEAR

Question

How to Find Beginning and End of Month depending on YEAR

luissantos

SSCertifiable

Points: 7391
More actions
August 6, 2007 at 4:18 am

#175690

Hello
I need in TSQL code to return the following example :
Total of Sails between : 01-11-2007 and 31-12-2007
Suppose Current system date is : getdate() = 28-11-2007
ClientName     FirstDayofMonth      LastDayofMonth      FirstDayofNextMonth
Alex                     1000,00               1500,00                     1600,00
Luis                       200,00               1400,00                     1400,00
The values that they want to return are :
FirstDayofMonth = 01-11-2007
LastDayofMonth  = 30-11-2007
FirstDayofNExtMonth = 01-12-2007

Depending if is bissextile year or not.
My application can call TSQL functions. How could use them in a TSQL query (Like the example above)

Many Thanks
Luis Santos

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

I don't know what you mean by "Depending if is bissextile year or not", but here's an example of the formulas you could use... replace @CurrentDate with GETDATE() and it will be "auto-magic".

SET DATEFORMAT dmy

DECLARE @CurrentDate DATETIME

SET @CurrentDate = '28-11-2007'

SELECT DATEADD(mm,DATEDIFF(mm,0,@CurrentDate),0) AS FirstDayOfMonth,

DATEADD(mm,DATEDIFF(mm,0,@CurrentDate)+1,0)-1 AS LastDayOfMonth,

DATEADD(mm,DATEDIFF(mm,0,@CurrentDate)+1,0) AS FirstDayOfNextMonth

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 2

Somthing like...

DECLARE @firstDayOfMonth DATETIME

DECLARE @lastDayOfMonth DATETIME

DECLARE @firstDayOfNextMonth DATETIME

SELECT @firstDayOfMonth = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

SELECT @firstDayOfNextMonth = DATEADD(mm, 1, @firstDayOfMonth)

SELECT @lastDayOfMonth = DATEADD(dd, -1, @firstDayOfNextMonth)

SELECT @firstDayOfMonth [FirstDayOfMonth], @lastDayOfMonth [LastDayOfMonth], @firstDayOfNextMonth [FirstDayOfNextMonth]

James

--
James Moore
Red Gate Software Ltd

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 3

Bah.. beaten by Jeff.. that man is unstoppable

--
James Moore
Red Gate Software Ltd

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Heh... oh what a smile your brought to this ol' man's face this morning! Thanks for the laugh, James!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

By the way... not my job to say so but I like the format of your code... nice and easy easy to read even when not in Courier

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

luissantos SSCertifiable Points: 7391 More actions · Answer 6

Hello Jeff

Thanks for your reply, when i say "Depending if is bissextile year or not", it´s because February could be have 28 days or less depending on the year.

How can do that

Thanks

Luis Santos

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

The formulas that both James and I wrote will automatically take care of that...

SET DATEFORMAT dmy

DECLARE @CurrentDate DATETIME

SET @CurrentDate = '27-02-2004' --Leap year

SELECT DATEADD(mm,DATEDIFF(mm,0,@CurrentDate),0) AS FirstDayOfMonth,

DATEADD(mm,DATEDIFF(mm,0,@CurrentDate)+1,0)-1 AS LastDayOfMonth,

DATEADD(mm,DATEDIFF(mm,0,@CurrentDate)+1,0) AS FirstDayOfNextMonth

SET @CurrentDate = '27-02-2005' --Non leap year

SELECT DATEADD(mm,DATEDIFF(mm,0,@CurrentDate),0) AS FirstDayOfMonth,

DATEADD(mm,DATEDIFF(mm,0,@CurrentDate)+1,0)-1 AS LastDayOfMonth,

DATEADD(mm,DATEDIFF(mm,0,@CurrentDate)+1,0) AS FirstDayOfNextMonth

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

This is a view I often use

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86769

N 56°04'39.16"
E 12°55'05.25"

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

I use a slightly different method for the last day of the month:

select
        dateadd(mm,datediff(mm,0,a.Date),0)     as FirstDayOfMonth,
        dateadd(mm,datediff(mm,-1,a.Date),-1)   as LastDayOfMonth,
        dateadd(mm,datediff(mm,0,a.Date)+1,0)   as FirstDayOfNextMonth
from
       ( select Date = getdate() ) a