?
Solved

SQL Query to select first day and last day of month

Posted on 2008-06-17
8
Medium Priority
?
5,910 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1200 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 400 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 400 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

801 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