SQL Add new timestamp if DATE is between (Kinda Static Date) Condition

So I need to add a timestamp in the result set if the record falls in this condition.

Example:

• Using todays date of "07-06-2011" as a comparison against a datetime, I need to add a timestamp to the result set if the record falls between the timestamps of MARCH 10th and APRIL 11th 2011

• Using a timestamp of "08-01-2011"as a comparison against a datetime, I need to add a timestamp to the result set if the record falls between APRIL 10th and MAY 11th 2011

and so on...

Having trouble with the syntax of the and how to calculate the specific dates, Any Ideas?

Example Data running for date: "07-06-2011"

• any date for the month of July should work but the March 10, 2011 and April 11, 2011 should stay the same until August. Then it should be the next two concurrent months

.

date_feild       new_date_feild
------------     --------------
"03-09-2011" 
"03-15-2011"     "07-06-2011"
"04-10-2011"     "07-06-2011"
"04-15-2011      

Pseudo Query:

SELECT date_feild, CASE
    WHEN date_field BETWEEN (three months ago on the 10th) AND (two months ago on the 11t开发者_如何学编程h)
    THEN NOW() AS new_date_feild
END
FROM tbl_name
WHERE other_conditions

I guess I would like to know how to get these dates in this format:

• three months ago on the 10th
• two months ago on the 11th

UPDATE:

Well I have concocted this mess of a query, can anyone else help it along?

SELECT TO_DATE(
    TEXTCAT(
        TEXTCAT(
            TEXTCAT(
                date_part('YEARS', CURRENT_DATE - INTERVAL '3 MONTHS'),
            '-'),
        date_part('MONTHS', CURRENT_DATE - INTERVAL '3 MONTHS')),
    '-10'),'YYYY-MM-DD') AS previous_date,
TO_DATE(
    TEXTCAT(
        TEXTCAT(
            TEXTCAT(
                date_part('YEARS', CURRENT_DATE - INTERVAL '3 MONTHS'),
            '-'),
        date_part('MONTHS', CURRENT_DATE - INTERVAL '3 MONTHS')),
    '-10'),'YYYY-MM-DD') + INTERVAL '1 MONTH' + INTERVAL '1 DAY' AS next_previous_date

Try using DateTime, or just Date, instead. Its easier for comparisons. Had a similar problem myself, though much less involved.

Here's an easy link to different ways and efficiency: http://demiliani.com/blog/archive/2006/01/19/3384.aspx

It's also fairly easy to get the current date in java from this, though it took me forever to figure it out. Here's how I did it:

//Get current DateTime
    java.util.Date javaDate = new java.util.Date();
    long javaTime = javaDate.getTime();
    java.sql.Date sqlDate = new java.sql.Date(javaTime);
    java.sql.Time sqlTime = new java.sql.Time(javaTime);
    String dateTime = sqlDate.toString() + " " + sqlTime.toString();

It's a bit weird, but sql seems to deal with this a lot better.

Well here is what I have but I'm not really happy with it:

SELECT TO_DATE(
    TEXTCAT(
        TEXTCAT(
            TEXTCAT(
                date_part('YEARS', CURRENT_DATE - INTERVAL '3 MONTHS'),
            '-'),
        date_part('MONTHS', CURRENT_DATE - INTERVAL '3 MONTHS')),
    '-10'),'YYYY-MM-DD') AS previous_date,
TO_DATE(
    TEXTCAT(
        TEXTCAT(
            TEXTCAT(
                date_part('YEARS', CURRENT_DATE - INTERVAL '3 MONTHS'),
            '-'),
        date_part('MONTHS', CURRENT_DATE - INTERVAL '3 MONTHS')),
    '-10'),'YYYY-MM-DD') + INTERVAL '1 MONTH' + INTERVAL '1 DAY' AS next_previous_date