October 13, 2009 at 4:31 pm
krypto69 (10/13/2009)
is this wrong?original way:
WHERE
isnull(datediff(dd,ld.delq_dt,getdate()),0) < 30
this:
WHERE
isnull(datediff(dd,ld.delq_dt,'2009-09-30'),0) < 30
Now I'm totally confused...
During the last couple hours I've been trying to explain how to get the data from the previous month.
quote from one of my previous posts:
WHERE ld.delq_dt < dateadd(mm, datediff(mm, 0, '2009-10-13'), 0)
AND
ld.delq_dt >= dateadd(mm, datediff(mm, 0, '2009-10-13') - 1, 0)
It's basically the same proposal as Jeff made later on, he just explained the whole concept from the very beginning.
What is the reason to ignore the help you've got so far and simply repeat the question from several posts before???? If you did'nt understand how it works, just ask for a more detailed explanation.
The reason your two queries from above will return different results is caused by the fact that one query is based on the current date whereas the other one will always check against the last day of September. The two queries would have had the same results on September 30th 2009.
Please take the time and re-read the whole thread. The answer to your question has been posted more than once.
If you need addtl. assistance please provide sample data as describe in the first link in my signature. It might be easier to explain how it works if we'd have data you're familiar with to make it easier to understand.
October 13, 2009 at 6:37 pm
Lutz - yes, I meant to put in my response that you had already pointed out this solution. I just wanted to review the concepts for the OP so they understand why the way they approached the solution may not work the way they expected.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 14, 2009 at 7:23 am
I didn't mean to upset anyone.
I was just trying to understand why it didn't work, now I do understand.
I really appreciate you guys taking the time to help.
Good karma to you all.
October 14, 2009 at 7:26 am
I love this method only because I can leave it in place and it will always get the previous month.
but
when I use
WHERE ld.delq_dt >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) - 1, 0) -- First of previous month
AND DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
I get
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
October 14, 2009 at 7:42 am
krypto69 (10/14/2009)
I love this method only because I can leave it in place and it will always get the previous month.but
when I use
WHERE ld.delq_dt >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) - 1, 0) -- First of previous month
AND DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
I get
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Oops, my fault - I missed part of the statement. It should be:
WHERE ld.delq_dt >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) - 1, 0) -- First of previous month
AND ld.delq_dt < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 14, 2009 at 2:53 am
It would be
< dateadd(d,-day(getdate())+1, getdate())
December 14, 2009 at 2:58 am
it would be
< dateadd(d,-day(getdate())+1, getdate())
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply