Working with months - 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 2000 Forums

Transact-SQL (2000)

Working with months

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-04-04 : 11:12:23

Hi, i want to include a where cluase where i look at the "month" column in my table and return all the pervious months name.

Example if i run the script now i should get march

Here is what i have so far but its not right
declare @month nvarchar
set @month = -1

select [month]
from tbl_History
where [month] = @month

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 11:15:26

select *
from tbl_History
where [month] < DATEPART(MONTH, CURRENT_TIMESTAMP)

But that will get you ALL history for all years, with months 1..(previous month), aka
January-March for ALL years.

Please post DDL for your tbl_History table and maybe we can think of something better for you.

Peter Larsson
Helsingborg, Sweden

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-04-04 : 11:44:15

I've decided to work with the date and job the job each night rather than at the start of the next month, as i feel i would get a time out on the connection because the oracle server has a cpu usage on it

so i use

WHERE CREATED_DT >= convert(datetime,convert(char(8),@Now,112))

and i'll set @now to -1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 12:05:22

If you get that statement to work at all, you will get the date of December 31, 1899.
And I think all CREATED_DT is after that date, right?

Try this

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

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 00:32:18

Is this?

declare @month int
set @month = -1

select month(Created_dt)
from tbl_History
where Created_dt>= Dateadd(month,@month,Created_dt)
and Created_dt<Dateadd(month,@month+1,Created_dt)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 01:46:48

where Created_dt>= Dateadd(month,@month,Created_dt) -- Will always be true if @Month = -1
and Created_dt<Dateadd(month,@month+1,Created_dt) -- Will never be true if @month = -1

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources