Finding Current Month's First Day& Last Day

Question

Post reply

Finding Current Month's First Day& Last Day

karthik M

One Orange Chip

Points: 29139
More actions
December 21, 2007 at 12:37 am

#62326

Hi,
1) How to find out the current month's first Date ?
2) How to find out the current month's Last Date ?
3) How to find out the Previous (or) Next month's Last Date ?
4) How to find out the Previous (or) Next month's First Date ?
My friend faced the above questions in his recent interview.
I have tried to find out the solution by using DateFunctions.But,I Can't.:doze:
Inputs are highly appreciable!
karthik

Viewing 15 posts - 1 through 15 (of 45 total)

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

DECLARE

@FirstOfThisMonth DATETIME,

@LastOfThisMonth DATETIME,

@FirstOfLastMonth DATETIME,

@LastOfLastMonth DATETIME,

@FirstOfNextMonth DATETIME,

@LastOfNextMonth DATETIME

SELECT

@FirstOfThisMonth =

CAST (

CAST ( YEAR (GETDATE()) AS VARCHAR ) + '-' +

CAST ( MONTH (GETDATE()) AS VARCHAR ) + '-' +

'01' AS DATETIME

)

SELECT @LastOfThisMonth =

DATEADD( "m", 1, @FirstOfThisMonth)-1

SELECT @FirstOfLastMonth =

DATEADD( "m", -1, @FirstOfThisMonth)

SELECT @LastOfLastMonth = @FirstOfThisMonth -1

SELECT @FirstOfNextMonth =

DATEADD( "m", 1, @FirstOfThisMonth)

SELECT @LastOfNextMonth =

DATEADD( "m", 2, @FirstOfThisMonth)-1

SELECT

@FirstOfThisMonth AS FirstOfThisMonth,

@LastOfThisMonth AS LastOfThisMonth,

@FirstOfLastMonth AS FirstOfLastMonth,

@LastOfLastMonth AS LastOfLastMonth,

@FirstOfNextMonth AS FirstOfNextMonth,

@LastOfNextMonth AS LastOfNextMonth

.

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Thanks.But,is it possible to get lastmonth's lastdate without using localvariables ?

I mean i want to find last month's last date only ? I don't required the current month's first,last month.Just using a single query, i want to find out the last month's last date.

karthik

Sanjay Pandey-195693 Valued Member Points: 68 More actions · Answer 3

Using Datefunctions.

select getdate()-day(getdate()) as LastDayLastMth, getdate()-(day(getdate())-1) as FirstDayThisMth,dateadd(m,1,getdate())-day(dateadd(m,1,getdate())) as LastDayThisMth,dateadd(m,1,getdate())-day(dateadd(m,1,getdate()))+1 as FirstDayNextMth

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 4

http://www.sqlservercentral.com/Forums/Topic397634-8-1.aspx

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

karthikeyan (12/21/2007)
Thanks.But,is it possible to get lastmonth's lastdate without using localvariables ?
I mean i want to find last month's last date only ? I don't required the current month's first,last month.Just using a single query, i want to find out the last month's last date.

Karthik,

It's just an example... the important part is the formulas... just use the formulas in your code. Think outside the box a bit... analyze the code and figure it out. That's what makes a strong developer is being able to take an example and make simple changes to it to get it to do what you want.

--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)

Sanjay Pandey-195693 Valued Member Points: 68 More actions · Answer 6

Hi Karthik,

Refer to Jeff's mail above. That is how majority of us learned. You have been provided 2 approaches. One using local variable and another without them.

Any way, if you still want the code, use the below one.

select getdate()-day(getdate()) as LastDayLastMth

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

This will do it.

select

FirstOfThisMonth =

dateadd(month,datediff(month,0,getdate()),0),

LastOfThisMonth =

dateadd(month,datediff(month,-1,getdate()),-1),

FirstOfLastMonth =

dateadd(month,datediff(month,0,getdate())-1,0),

LastOfLastMonth =

dateadd(month,datediff(month,-1,getdate())-1,-1),

FirstOfNextMonth =

dateadd(month,datediff(month,0,getdate())+1,0),

LastOfNextMonth =

dateadd(month,datediff(month,-1,getdate())+1,-1)

Start of Time Period Functions:

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

Start of Week Function:

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

End Date of Time Period Functions:

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

End of Week Function:

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

Phil Factor SSC-Insane Points: 20244 More actions · Answer 8

Your friend should have read Robyn Page's Date/time workbench[/url] before the interview

Best wishes,
Phil Factor

karthik M One Orange Chip Points: 29139 More actions · Answer 9

Michael,

select FirstOfThisMonth =dateadd(month,datediff(month,0,getdate()),0),LastOfThisMonth =dateadd(month,datediff(month,-1,getdate()),-1),FirstOfLastMonth =dateadd(month,datediff(month,0,getdate())-1,0),LastOfLastMonth =dateadd(month,datediff(month,-1,getdate())-1,-1)

Thanks.only the first two was worked.

I have made some changes in the last 2 statements.

FirstOfLastMonth =dateadd(month,datediff(month,0,dateadd(mm,-1,getdate())),0),

LastOfLastMonth =dateadd(month,datediff(month,-1,dateadd(mm,-1,getdate()),-1)

It gave last month's firstdate & lastdate.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 10

Also,Dateadd(month,0,0)

Here,

1st - Zero stands for Number to be added to the month part.

2nd -Zero Stands for 1900-01-01(Default Datetime).

It will show 1900-01-01.

Dateadd(month,0,-1)

Here,

1st - Zero stands for Number to be added to the month part.

2nd -Zero Stands for 1899-12-31.

It will show 1899-12-31

Am i correct ?

karthik

ZeyNep-583656 Valued Member Points: 71 More actions · Answer 11

declare @Year int

declare @Month int

select @Year=datepart(year,getdate()),@Month=datepart(month,getdate())

declare @NextMonth int

if @Month<>12

set @NextMonth=@Month+1

else

set @NextMonth=1

select convert(datetime, convert(char(4),@Year)+right('0'+convert(varchar,@Month),2)+'01')FirstDay

,dateadd(d,-1,convert(datetime,convert(char(4),@Year)+right('0'+convert(varchar,@NextMonth),2)+'01'))LastDay

http://transactsql.blogspot.com/

Phil Factor SSC-Insane Points: 20244 More actions · Answer 12

[font="Courier New"]--1) How to find out the current month's first Date ?

SELECT CONVERT(DATETIME,'01 '+RIGHT(CONVERT(CHAR(11),GETDATE(),113),8))

--2) How to find out the current month's Last Date ?

SELECT DATEADD(DAY,-1,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8))

--3) How to find out the Previous (or) Next month's Last Date ?

SELECT DATEADD(DAY,-1,'01 '+RIGHT(CONVERT(CHAR(11),GETDATE(),113),8))--previous

SELECT DATEADD(DAY,-1,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,2,GETDATE()),113),8))--next

--4) How to find out the Previous (or) Next month's First Date ?

SELECT CONVERT(DATETIME,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8))--previous

SELECT CONVERT(DATETIME,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8))--next

[/font]

Do I get the job?

Best wishes,
Phil Factor

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

karthikeyan (12/31/2007)
Michael,
select FirstOfThisMonth =dateadd(month,datediff(month,0,getdate()),0),LastOfThisMonth =dateadd(month,datediff(month,-1,getdate()),-1),FirstOfLastMonth =dateadd(month,datediff(month,0,getdate())-1,0),LastOfLastMonth =dateadd(month,datediff(month,-1,getdate())-1,-1)
Thanks.only the first two was worked.
I have made some changes in the last 2 statements.
FirstOfLastMonth =dateadd(month,datediff(month,0,dateadd(mm,-1,getdate())),0),
LastOfLastMonth =dateadd(month,datediff(month,-1,dateadd(mm,-1,getdate()),-1)
It gave last month's firstdate & lastdate.

You must have made a mistake when you copied my code. I just tested it again and it is all correct.

I test the code you posted, and the code for LastOfLastMonth is not even correct syntax.

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

Phil Factor (12/31/2007)
[font="Courier New"]--1) How to find out the current month's first Date ?
SELECT CONVERT(DATETIME,'01 '+RIGHT(CONVERT(CHAR(11),GETDATE(),113),8))
--2) How to find out the current month's Last Date ?
SELECT DATEADD(DAY,-1,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8))
--3) How to find out the Previous (or) Next month's Last Date ?
SELECT DATEADD(DAY,-1,'01 '+RIGHT(CONVERT(CHAR(11),GETDATE(),113),8))--previous
SELECT DATEADD(DAY,-1,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,2,GETDATE()),113),8))--next
--4) How to find out the Previous (or) Next month's First Date ?
SELECT CONVERT(DATETIME,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8))--previous
SELECT CONVERT(DATETIME,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8))--next
[/font]
Do I get the job?

Your code produces correct results, but I have found in testing that doing this by converting to strings and back to datetime is a much slower method that using the nested dateadd/datediff method that I posted. The dateadd/datediff code is also shorter to write. I reposted your code below and followed each with the equivalent using nested dateadd/datediff to show the difference in code length.

--1) How to find out the current month's first Date ?

SELECT CONVERT(DATETIME,'01 '+RIGHT(CONVERT(CHAR(11),GETDATE(),113),8))

select dateadd(month,datediff(month,0,getdate()),0)

--2) How to find out the current month's Last Date ?

SELECT DATEADD(DAY,-1,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8))

select dateadd(month,datediff(month,-1,getdate()),-1)

--3) How to find out the Previous (or) Next month's Last Date ?

SELECT DATEADD(DAY,-1,'01 '+RIGHT(CONVERT(CHAR(11),GETDATE(),113),8))--previous

select dateadd(month,datediff(month,-1,getdate())-1,-1)

SELECT DATEADD(DAY,-1,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,2,GETDATE()),113),8))--next

select dateadd(month,datediff(month,-1,getdate())+1,-1)

--4) How to find out the Previous (or) Next month's First Date ?

SELECT CONVERT(DATETIME,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8))--previous

select dateadd(month,datediff(month,0,getdate())-1,0)

SELECT CONVERT(DATETIME,'01 '+RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8))--next

select dateadd(month,datediff(month,0,getdate())+1,0)