Solved

SQL Query to select first day and last day of month

Posted on 2008-06-17
8
5,903 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now