Previous months information
-
Please forgive my brain dead question:
I have a tabel that tackes changes to the database. In that table I have a datetime field. I want to run a report on the first of everymonth that gets only the records form the previous month.
I have thought about doing a between, or greater then less then. Maybe a select datepart(mounth, Getdate()) -1. Not sure what is best. i ould like to look as some examples.
Feeling kinda stupid on this one.
Stacey W. A. Gregerson -
You were close to the answer:
If you run on the first of every month:
select * from table where datapart(m,datecolumn) = datepart(m,getdate() - 1)
will work. However, it will ONLY work on the first of the month.
select * from table where datapart(m,datecolumn) = datepart(m,getdate()-datepart(d,getdate()))
will work on any day of the month, returning the records for the previous month.
(Mind you, this is off the top of my head, so it does bear testing.)
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
select * from
where datepart(m,datecolumn) = datepart(m,getdate()) - 1
will select previous month on any day of the month
but will result in either a table scan (or index scan if datecolumn is indexed)
if datecolumn is indexed then
declare @startdate datetime,@enddate datetime
set @enddate = convert(char(8),getdate(),120) + '01'
set @startdate = dateadd(m,-1,@enddate)
select * from
where datecolumn >= @startdate
and datecolumn < @enddate
will use index seek
Far away is close at hand in the images of elsewhere.
Anon. -
please expalint the (convert(char(8),getdate(),120) + '01' )
Thx
Stacey W. A. Gregerson -
THis is what I came up with. Granted it is more then my original request but I thought I would play allitttle.
Thanks for the suggestions
Stacey W. A. Gregerson -
help if I show the code.
--code starts
DECLARE
@CurDay int,
@CurWeek int,
@CurMonth int,
@CurYear int,
@CurPreviousDay int,
@CurPreviousWeek int,
@CurPreviousMonth int,
@CurPreviousYear int
SET @CurDay = DATEPART(dd, GETDATE())
SET @CurWeek = DATEPART(wk, GETDATE())
SET @CurMonth = DATEPART(mm, GETDATE())
SET @CurYear = DATEPART(yyyy, GETDATE())
SET @CurPreviousDay = DATEPART(dd, dateadd(dd, -1, GETDATE()))
SET @CurPreviousWeek = (@CurWeek - 1)
SET @CurPreviousMonth = (@CurMonth - 1)
PRINT 'Yesterday'
SELECT Count(*) FROM dba.dbo.rptvdat
WHERE DATEPART(dd, InstallDate) = @CurPreviousDAY
AND DATEPART(yyyy, InstallDate) = @CurYear
Print ''
PRINT 'Last Week'
SELECT Count(*) FROM dba.dbo.rptvdat
WHERE DATEPART(Wk, InstallDate) = @CurPreviousWeek
AND DATEPART(yyyy, InstallDate) = @CurYear
Print''
PRINT 'Last Month'
SELECT Count(*) FROM dba.dbo.rptvdat
WHERE DATEPART(mm, InstallDate) = @CurPreviousMonth
AND DATEPART(yyyy, InstallDate) = @CurYear
--code ends
Stacey W. A. Gregerson -
please expalint the (convert(char(8),getdate(),120) + '01' ) Convert allows you to convert dates to specific formats, eg style 120 will convert date to yyyy-mm-dd hh:mi:ss. By converting to char(8) sql will truncate the output to 8 chars (eg '2004-10-25 00:00:00' to '2004-10-'). Then by adding '01' and storing the result in a datetime variable you will get the date of the first day of the month for todays date.
Far away is close at hand in the images of elsewhere.
Anon. -
This is my preferred method.
DATEADD(m,DATEDIFF(m,0,GETDATE()),0)
This will give you the first day of this month midnight.
DATEADD(m,-1,DATEADD(m,DATEDIFF(m,0,GETDATE()),0))
This will give you day one of the previous month midnight.
Then do
SELECT .... WHERE DateField >= DATEADD(m,-1,DATEADD(m,DATEDIFF(m,0,GETDATE()),0)) AND DateField < DATEADD(m,DATEDIFF(m,0,GETDATE()),0)
-
I like this one Antares686. It is much simpler then what I tried.
Thank you to all that have helped me with this one. As you can tell i have not been doing allot of Tsql so this really helps. I need to do more. I am forcing myself now with some linked server and backup queries. So you may see more question in the future.
Thanks again for the help.
Stacey W. A. Gregerson -
Antares solution is very slick, though it may be difficult to understand how it works and hard to read if you don't know how the functions involved work in this 'special' case.
Another (perhaps 'lighter') way to do this stuff;
-- This is a sample matrix showing how to find out dates of month boundries
-- from any given point in time.
set nocount on
declare @date datetime
set @date = getdate() -- the point in time from which to measure
print '''Today''s date'' is: ' + convert(char(10), @date, 121)
print ' '
-- date of the 1st of the current month
select convert(char(6), @date, 112) + '01' as '1st this month'
-- date of the last day of current month
select dateadd(day, -1, dateadd(month, 1, convert(char(6), @date, 112) + '01')) as 'last this month'
-- date of the 1st of the previous month
select dateadd(month, -1, convert(char(6), @date, 112) + '01') as '1st of last month'
-- date of the last day of the previous month
select dateadd(day, -1, convert(char(6), @date, 112) + '01') as 'last of last month'
-- date of the 1st of the next month
select dateadd(month, 1, convert(char(6), @date, 112) + '01') as '1st of next month'
-- date of the last day of the next month
select dateadd(day, -1, dateadd(month, 2, convert(char(6), @date, 112) + '01')) as 'last of next month'
set nocount off
/Kenneth
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply