Date <= DATEADD(MONTH,-1,GetDate()) Issue?! 1

[tqeonline]

Hey everyone!

So in my code I have this

AND TESTCYCL.TC_EXEC_DATE < DATEADD(MONTH, -1, GetDate())

The results are coming back invalid

Date Manual Automated
2010-09-01 258 277

But if you put

AND TESTCYCL.TC_EXEC_DATE < '2010-10-01'

These results are valid

Date Manual Automated
2010-09-01 5213 3260

For some reason there is a discrepancy in the codes that isn't returning accuratly.

Maybe its because it's the first day of the month... any ideas?

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

I see a couple things.

IN the first block, you have DateAdd(Month, -1, GetDate()). Since today is Oct 1, 2010, when you subtract 1 month, you get [!]September[/!] 1, 2010.

In the second block, you are hard coding [!]October[/!] 1, 2010.

The next thing I see is... time. With the first block, you will have a time component. If today is Oct 1, 2010 9:15 AM, and then you subtract 1 month, you will have Sep 1, 2010 9:15 AM.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

right because hardcoding '<10-1-2010' gives me the desired results that means that everything less than OCTOBER is returned.

Which should be the same thing that

AND TESTCYCL.TC_EXEC_DATE < DATEADD(MONTH, -1, GetDate())

Does... but it doesn't.

So really how would i make it say < CURRENT MONTH

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[gmmastros]

There is a trick to getting the start of the month. The trick is....

Select DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)

so...

AND TESTCYCL.TC_EXEC_DATE < DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)

Let's go over how this works.

[tt]DateAdd(Month, DateDiff(Month, 0, [!]GetDate()[/!]), 0)[/tt]

GetDate() returns the current date and time.

[tt]DateAdd(Month, DateDiff(Month, [!]0[/!], GetDate()), 0)[/tt]

The 0 in this case represents SQL Server's 0 date, which is Jan 1, 1900.

[tt]DateAdd(Month, [!]DateDiff(Month, 0, GetDate())[/!], 0)[/tt]

DateDiff returns an integer. In this case, we are returning an integer number of months that have elapsed since Jan 1, 1900. For October 2010, this will return the value 1329.

For explanation purposes, let's replace the DateDiff function with the value that it returns. You end up with:


[tt]DateAdd(Month, [!]1329[/!], 0)[/tt]

[tt]DateAdd(Month, 1329, [!]0[/!])[/tt]

Again, the 0 represents Jan 1, 1900. So the DateAdd part is adding 1329 months to Jan 1, 1900 which results in Oct 1, 1900.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

That is an awesome work around!

Is what I changed it to that got it working. But yours does it with better logic.

(MONTH(TESTCYCL.TC_EXEC_DATE) <> MONTH(CURRENT_TIMESTAMP) OR YEAR(TESTCYCL.TC_EXEC_DATE) <> YEAR(CURRENT_TIMESTAMP))

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008