Previous Month Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Previous Month Query

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-07-23 : 12:48:15

Hi
I am trying to run some data extraction for the first of the month for the previous month. The query I have is select datepart(month,getdate())-1.
this is great till it will reach January. then the answer to this would be 0 and the results returned are nothing..
any ideas how to handle this?

Regards
Paresh Motiwala
Boston, USA

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-23 : 13:08:42

It would help if you posted what your data looks like. I am assuming you have a datetime field to work with. If you have a year field and a month field, you'll have to modify this

DECLARE @thisMonth datetime
SET @thisMonth = '01/01/2007'
SET @lastMonth DATEADD(m,-1,CONVERT(datetime,convert(varchar(2),MONTH(@thisMonth))+'/1/'+ convert(varchar(4),YEAR(@thisMonth)) ))

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 14:03:20

SELECT *
FROM Table1
WHERE Col1 >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0),
AND Col1 < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)

Peter Larsson
Helsingborg, Sweden

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-23 : 14:50:47

Cool. I don't fully get it but it's cool. I see that
select DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) is the same as
DATEDIFF(MONTH, '01/01/1900', CURRENT_TIMESTAMP).
When the second argument is an integer, does sql always take it to mean days added to '01/01/1900'?

Jim

P.S. Please ignore this question if it's a candidate for the twit list

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-23 : 16:02:14

When you convert 0 to a datetime, it is converted to 1900-01-01 00:00:00.000

CODO ERGO SUM

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-07-23 : 16:09:12

Peso's script worked.
Not sure how though, I am trying to digest the script.
Thanks to both of you for your help.

Regards
Paresh Motiwala
Boston, USA

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-24 : 06:30:00

Thanks,

Everyday I learn something that somebody else already knew!
Jim

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2008-09-10 : 15:26:57

just so I understand this query better, how would I then use it for records more than one full year old?

Regards
Paresh Motiwala
Boston, USA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-10 : 16:00:41

don't write:


WHERE date BETWEEN <COMPLICATED EXPRESSION 1> AND <COMPLICATED EXPRESSION 2>

because it's hard to test and to know for sure how those date expressions are working. Write this instead:


declare @Start datetime, @End dateTime
set @Start = <COMPLICATED EXPRESSION 1> 
set @End = <COMPLICATED EXPRESSION 2> 

select @start as [Start Date], @End as [End Date]

-- select ... from .. where Date between @start and @end

This way you can test your formulas, make sure they are working, tweak them to do things like last week, last month, last year, next month, etc, to be sure they all work well. then, you can uncomment the SELECT portion of the script when you are sure you are setting your start/end variables properly.

This is a really good practice to get into to be sure that things are working as you expect, especially when getting code from someone else in a online forum that you don't completely 100% understand.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources