Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query to select first day and last day of month

Posted on 2008-06-17
8
Medium Priority
?
5,911 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 your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

604 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