Help with date selction based on Month and Year 1

[vega83]

Friends, I need help on below selection critieria in DB2.
Below where clause works fine, but only If I pass my paramter in DATE or DATE TIME format. I wan to give my end user an option to select by Month Year format May2009 instead of 05/23/2009 or so.

Below selection criteria gives me data for last 12 months.

WHERE DATE(ADDED_DT) >= ((DATE({?Report_Date}) - Day(DATE({?Report_Date})) day)+1 day) - 12 month
AND DATE(ADDED_DT) <= (((DATE({?Report_Date}) - Day(DATE({?Report_Date})) day)+1 day) + 1 month) - 1 day

ANy help on how to get that?
Thanks a ton,
V

 

[SkipVought]

Hi,

A date is not a month or a month and year. It is year, month and day.

If you have a month and year, you must use those values to product a date; presumably either the FIRST or LAST day of the given month and year.

Alternatively, convert the date value to month and year to compare with your values.

Keep in mind that the format May2009 will produce incorrect results for use in cirteria, as Apr2009 colates BEFORE Jan2009. Rather, use a yyyymmm format.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[LoriDurham]

Skip How can we format the date field into yyyymm, any ideas ?

 

[blom0344]

Year(Date)*100+Month(date)

cast(Year(Date)*100+Month(date) as varchar(6))

Ties Blom

 

[LoriDurham]

Blom Many thanks, one final question how to we again break this paramter in where condtion.
Year(Date)*100+Month(date) has worked, but getting error for
cast(Year(Date)*100+Month(date) as varchar(6))

Below is where condtion where I want to break
Year(Date)*100+Month(date) accordingly

WHERE DATE(ADDED_DT) >= ((DATE({?Report_Date}) - Day(DATE({?Report_Date})) day)+1 day) - 12 month
AND DATE(ADDED_DT) <= (((DATE({?Report_Date}) - Day(DATE({?Report_Date})) day)+1 day) + 1 month) - 1 day

 

[LoriDurham]

any ideas friends how to split 200908 accordingly in the above formula?

 

[blom0344]

How about:

Year(Added_DT)*100+Month(Added_DT)
>= Year({?Report_Date})*100-100+Month({?Report_Date})
and
Year(Added_DT)*100+Month(Added_DT)
<= Year({?Report_Date})*100-100+Month({?Report_Date})

Ties Blom

 

[LoriDurham]

Great Idea, thanks a TON

 

[blom0344]

My example should read:

Year(Added_DT)*100+Month(Added_DT)
>= Year({?Report_Date})*100-100+Month({?Report_Date})
and
Year(Added_DT)*100+Month(Added_DT)
<= Year({?Report_Date})*100+Month({?Report_Date})

Success!

Ties Blom