Solved

SQL Query to select first day and last day of month

Posted on 2008-06-17
8
5,904 Views
Last Modified: 2012-05-05
hi

Can anyone give me an example of creating a variable to select the first day and last day of each month please?

Adam
0
Comment
Question by:ac_davis2002
8 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 300 total points
ID: 21802188
This should do it:


declare @date datetime, @first datetime, @last datetime
set @date = getdate()

set @first = cast(month(@date) as varchar(2)) + '/1/' + cast(year(@date) as varchar(4))
set @last = dateadd(d, -1, dateadd(m, 1, @first))
select @first, @last

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 21802197
DECLARE @month int , @Year int
DECLARE @firstDay datetime, @LastDay datetime
SELECT @month = 5 ,@Year = 2008

SELECT @FirstDay = CAST(@Year AS varchar) + '-'+CAST(@Month AS varchar)+'-01'
SELECT @LastDay = DATEADD(MONTH,+1,@firstDay) -1
SELECT @firstDay,@LastDay
0
 
LVL 11

Assisted Solution

by:CMYScott
CMYScott earned 100 total points
ID: 21802251
you get the first day by building a string based on a known month and year and adding in the day as '01' - then cast the string as a datetime

you get the last day, by using dateadd against the first-day value and adding a month to it, which gives you the first day of the following month - then use Dateadd to subtract one day.

declare @firstday datetime
declare @lastday datetime
declare @month int
declare @year int
 
set @year = '2008'
set @month= '01'
 
 
SET @firstday = cast(cast(@month as varchar(2)) + '/01/' + cast(@year as varchar(4)) AS datetime)
select @firstday
set @lastday = dateadd(d,-1,dateadd(m,1,@firstday))
select @lastday

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:ac_davis2002
ID: 21802413
Hi chaps

this is great but using chapmandew example I would like to set @date  to a datetime column from a table called ctcontracts.

for example

declare @date datetime,
@first datetime,
@last datetime
set @date = select fromdate from ctcontracts
set @first = cast(month(@date) as varchar(2)) + '/1/' + cast(year(@date) as varchar(4))
set @last = dateadd(d, -1, dateadd(m, 1, @first))
select @first, @last

but I am getting the following error

Incorrect syntax near the keyword 'select'.

Can you help me with this final step?
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 300 total points
ID: 21802429
declare @date datetime,
@first datetime,
@last datetime
SELECT @date = fromdate from ctcontracts
set @first = cast(month(@date) as varchar(2)) + '/1/' + cast(year(@date) as varchar(4))
set @last = dateadd(d, -1, dateadd(m, 1, @first))
select @first, @last
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 300 total points
ID: 21802447
you're probably better off doing this, though:

SELECT firstdate = cast(month(fromdate) as varchar(2)) + '/1/' + cast(year(fromdate) as varchar(4)) ,
lastdate = dateadd(d, -1, dateadd(m, 1, cast(month(fromdate) as varchar(2)) + '/1/' + cast(year(fromdate) as varchar(4))))
from ctcontracts
0
 

Author Comment

by:ac_davis2002
ID: 21802471
thanks chapmandew
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21802480
you're welcome
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSIS with VPN COnnection 2 76
How to search for strings inside db views 4 27
SSRS Enable Remote Errors 4 26
Replace the integer portion in CAST with a column 4 16
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question