The classic, "It was working..."

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)

The classic, "It was working..."

Author

Topic

sketchy
Starting Member

7 Posts

Posted - 2011-08-18 : 17:00:11

So I have this query I run against a phone log database. It's been running happily since the begining of 2011, but hasn't worked since. The report queries the logged data for the previous month

WHERE     (MONTH(Date) = MONTH(GETDATE())-1) AND (YEAR(Date) = YEAR(GETDATE()))

However,since the year 2011, this simply returns no results. If I change the "-1" to "-2" it grabs the data correct from two months before. If I change it to "-0" it captures the current month. If I force the year to something other than "2011" the previous month "-1" shows up perfectly. What in the daylights is going on? And how should I remedy it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-18 : 17:04:10

WHERE Date >= DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101')
AND Date < DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')

N 56°04'39.26"
E 12°55'05.63"

sketchy
Starting Member

7 Posts

Posted - 2011-08-18 : 17:47:56

quote:
Originally posted by SwePeso

WHERE Date >= DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101')
AND Date < DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')

N 56°04'39.26"
E 12°55'05.63"

Thanks SwePeso for the fast reply. I tried your statement, and here is what it does.
1. If I only include what is stated before the 'AND' it returns results.
2. If I only include what is stated after the 'AND' it returns results.
3. If I combine the two as you show, the query succeeds, but doesn't return any records. Just as my orginal formula did.

Any ideas?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-18 : 21:50:05

What is the data type of the column 'Date'?

Try hard-coding the range check as:

WHERE Date >= '20110701'
AND Date < '20110801'

If you don't get results, check the Date column to see values are being returned when you run with just the >= portion of the WHERE clause. My guess is that the Date column is not a datetime data type and that is causing the problems.

Jeff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 02:22:48

quote:
Originally posted by sketchy

quote:
Originally posted by SwePeso

WHERE Date >= DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101')
AND Date < DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')

N 56°04'39.26"
E 12°55'05.63"

Thanks SwePeso for the fast reply. I tried your statement, and here is what it does.
1. If I only include what is stated before the 'AND' it returns results.
2. If I only include what is stated after the 'AND' it returns results.
3. If I combine the two as you show, the query succeeds, but doesn't return any records. Just as my orginal formula did.

Any ideas?

he was suggesting to replace your current condition with above one. so use only above and dont add anything to it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-08-19 : 03:14:33

Database corruption wrecked your index?

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS

Kristen
Test

22859 Posts

Posted - 2011-08-19 : 03:15:35

quote:
Originally posted by visakh16

he was suggesting to replace your current condition with above one. so use only above and dont add anything to it

I don't think he did, did he? (as per his point 3)

Although I'm now thinking that MONTH(MyColumn) = MONTH(GetDate()) ain't going to use any index, so index corruption unlikely to have any bearing.

And I'm now with Jeff! ("My guess is that the Date column is not a datetime data type and that is causing the problems")

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 03:51:58

quote:
Originally posted by Kristen

quote:
Originally posted by visakh16

he was suggesting to replace your current condition with above one. so use only above and dont add anything to it

I don't think he did, did he? (as per his point 3)

Although I'm now thinking that MONTH(MyColumn) = MONTH(GetDate()) ain't going to use any index, so index corruption unlikely to have any bearing.

And I'm now with Jeff! ("My guess is that the Date column is not a datetime data type and that is causing the problems")

sorry i thought like by point 3 OP was trying to combine his condition with Peso's

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-08-19 : 04:28:38

No apology needed! I just didn't want your mind searching for solutions down possible dead ends ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 04:30:16

quote:
Originally posted by Kristen

No apology needed! I just didn't want your mind searching for solutions down possible dead ends ...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sketchy
Starting Member

7 Posts

Posted - 2011-08-19 : 10:19:02

Thanks for the great discussion everyone. Let me clear up some points.

The original suggestion provided by SwePeso was the only thing I was using. I split his statement into a couple of parts to demonstrate how it was behaving.

As for the column type, it is (and has always been) a "datetime" type.

Its been a bit bizzare, because this same SQL statement I had been using on the same procedure (bringing in some phone system logging in to SQL for some reports) since about 2007, with no problem. The data is dumped out to a csv, then imported into a table via SSIS. All of the data in the date column looks well formed. And I can manipulate the date query in just about every other way possible. ...It just seems to choke on getting the previous month in 2011.

Kristen
Test

22859 Posts

Posted - 2011-08-19 : 10:21:53

So did you run the database consistency check?

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS

sketchy
Starting Member

7 Posts

Posted - 2011-08-19 : 12:28:19

Yep, ran it twice. Once from tsql, and the other from a DB and the other from a defined maintenance plan. Checked out fine.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-08-19 : 12:54:25

Strange.

As date is a reserved word, have you tried replacing date with [date]?

sketchy
Starting Member

7 Posts

Posted - 2011-08-19 : 13:41:57

Okay everyone, I think I got it worked out. Due to the nature of some outside influences (phone system - dang nabit), it looks like that it just didn't capture the previous month. Thus, on my import jobs, there was not data for that particular month to import, and nothing to query against. Its all very weird, but all of you had great input, and I liked some of the ways that you broke down the queries against the date. Oh, and yes, I decided to also bracket the "date" field name so that the reservation for that same name doesn't get confused. Thanks again everyone.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-08-19 : 19:09:31

Does your original logic work at all in January of any year?

=======================================
The first rule of Tautology Club is the first rule of Tautology Club. -xkcd, (Honor Societies)

sketchy
Starting Member

7 Posts

Posted - 2011-08-20 : 00:08:21

No, in fact it doesn't. Good catch. I'm open to suggestions. Perhaps some of the other logic provided might work better?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-20 : 01:58:00

Try mine... Replace GETDATE() with any other date of your choice to fake a custom date.

N 56°04'39.26"
E 12°55'05.63"

sketchy
Starting Member

7 Posts

Posted - 2011-08-20 : 09:43:16