Solved

SQL Query to select first day and last day of month

Posted on 2008-06-17
8
5,907 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

726 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