Would you do this? Where Year(dt) = (year(sdt)-1)

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)

Would you do this? Where Year(dt) = (year(sdt)-1)

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-05-25 : 16:59:58

Hey all-
I seem to be having some problems with dates.

I don't like this, but it works for previous year.
WHERE YEAR(E_MASTER.EO_YEARMONTH) = (YEAR(@inDATE)-1)

Or is this a better solution?
WHERE YEAR(E_MASTER.EO_YEARMONTH) = YEAR(DATEADD(YY,-1,@inDATE))

Can I do the same with day and month (previous months and days)?
MONTH(DATEADD(MM,-1,@DATEONE))

I think a date range would be faster (1/1/20xx - 12/1/20xx), but that seems a little harder to compute for a previous month without some sort of lookup table for dates.

Any thoughts?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-25 : 17:05:03

What are you trying to do? Please provide an example.

Tara

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-05-25 : 17:49:11

I am after totals for previous year and month. Each entry is marked with an accounting date and I need to sum them up and give totals. I guess I was looking for suggestions on what is the best way to do this. I've given several examples of my WHERE clause, but am looking for some input from voices of experience.

Thanks,
Doug

Kristen
Test

22859 Posts

Posted - 2004-05-26 : 04:35:11

Won't MONTH(DATEADD(MM,-1,@DATEONE)) give you a bit of grief whilst you are in January (=1) and comparing with December (=12)?

I reckon you could do something like:


-- "113" format is "31 Dec 2004 ..." style
SELECT @StartOfThisMonth = '01' + SUBSTRING(CONVERT(varchar(24), @MyDate, 113), 3, 24)
SELECT @StartOfNextMonth = DATEADD(Month, 1, @StartOfThisMonth)

SELECT *
FROM   MyTable
WHERE      MyDateColumn >= @StartOfThisMonth
       AND MyDateColumn <  @StartOfNextMonth

Note that the END point is the FIRST day of the FOLLOWING month, and I select LESS THAN this. That allows for a value of 1 minute before midnight on the last day of the month to be included; and I don't have to know how many days in the month etc.

I can add


SELECT @StartOfLastMonth = DATEADD(Month, -1, @StartOfThisMonth)

and then my WHERE for last month is:


WHERE      MyDateColumn >= @StartOfLastMonth 
       AND MyDateColumn <  @StartOfThisMonth

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources