ORACLE 9I SQL QUERY DATE FIELD IN LAST FULL MONTH

Hello
Is it possible to retrieve data where the date field selected will always be in the previous month - eg,
for June I only want May's data and for July I only want June' s data etc . So, whenever my query is run I always get previous months data.
What is the syntax for this?
Regards
Phil
philsivyerAsked:
Who is Participating?
 
YANN0SConnect With a Mentor Commented:
Henka: What If sysdate is 1/1/2008?  to_number(to_char(sysdate,'mm'))-1 will return 0 not 12.

philsivyer: If you don't have any performance issues you can use the following expression:

TRUNC(t.dat_column,'MONTH') = ADD_MONTHS(TRUNC(SYSDATE,'MONTH'),-1)

If you have performance issues (and you have an index on dat_column you can use

t.dat_column >=  ADD_MONTHS(TRUNC(SYSDATE,'MONTH),-1)
AND t.dat_column < TRUNC(SYSDATE,'MONTH')
0
 
Helena Markováprogrammer-analystCommented:
You can use this:
select t.dat_column from t_table t
where to_number(to_char(sysdate,'mm'))-1=to_number(to_char(t.dat_column,'mm'));
0
 
Helena MarkováConnect With a Mentor programmer-analystCommented:
Thanks YANNOS :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.