current_date

[oakpark5]

I've got a sql string and what I'm trying to do is this:

select * from table where invdte between current_date and current_date - 3 months.

Basically I want everything thats between the date today and the date 3 months ago... just not sure how to get the three months ago part....

Software Engineer
Team Lead
damn bill gates, he has everything

 

[donutman]

Depends on what you mean by 3 months but give this a go:

[Blue]SELECT[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray][Fuchsia]Month[/Fuchsia][Gray],[/Gray][Gray]-[/Gray]3[Gray],[/Gray][Fuchsia]GetDate[/Fuchsia][Gray]([/Gray][Gray])[/Gray][Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[oakpark5]

got it, thanks!!!

Software Engineer
Team Lead
damn bill gates, he has everything

 

[EdwinGene]

oakpark5,

Just as a heads up you need to be aware of just exactly what you want and what your solution will return.

"SELECT DateAdd(Month,-3,GetDate())" for GetDate()'s of 05-28-2005 through 05-31-2005 will ALL return a result of 02-28-2005. And this will happen for any month where the month three months prior has less days.

This may be acceptable to you, but you should be aware that it will happen.

 

[EdwinGene]

(continuation...)

And on the flip side, dates like 11-29-2004 and 11-30-2004 will NEVER be returned. A GetDate() of 02-28-2005 will return 11-28-2004, and a GetDate() of 03-01-2005 will return 12-01-2004.

Again, I don't know if this is acceptable to you or not, but you should at least be aware of it.