CODEWITHSUNDEEP
sqloracle-databasedatedatetimeformat
Adam.h
Adam.h

Reputation: 1

Oracle 11g SQL - DD/MON/YYYY to MONTH DD, YYYY with WHERE month year clause

I'm trying to get records from orders placed in a certain month and year (February 2012). The column containing that data (Dtordered) is currently in DD/Mon/YYYY format. I need to display the date in MONTH DD, YYYY format. How would I go about this? I know I will need to use a WHERE clause to get the records from the date and year. In pseudo, here's what I'm attempting.

SELECT Idbasket, Idshopper, Dtordered(MONTH DD, YYYY)
FROM bb_basket
WHERE Dtordered(Mon)='Feb' AND Dtordered(YYYY)=2012;

How would I correct my formatting and conditions? Am I even close?

Upvotes: 0

Views: 3147

Answers (2)

Marcelo Rebouças
Marcelo Rebouças

Reputation: 699

One tip very simple using to_char function, look:

For Month:

to_char(YOUR_FIELD , 'MM') = 09

For Year:
to_char(YOUR_FIELD , 'YYYY') = 2014

For Day:

to_char(YOUR_FIELD , 'DD') = 25

For Description Month:

to_char(YOUR_FIELD , 'MON') = 'FEB'

or

to_char(YOUR_FIELD , 'MONTH') LIKE '%FEBRUARY%'

to_char funcion is suported by sql language and not by one specific database.

One adiction information. To get month description of atual date at oracle database:

select to_char(sysdate,'MON') from dual; -- today returns SET
select to_char(sysdate,'MONTH') from dual; --today returns SETEMBER

I hope help anybody more...

Abs!

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

Assuming Dtordered is stored as a DATE (I hope so, as it's a date)

[if it's not, you'll have to use TO_DATE(Dtordered, 'DD/Mon/YYYY') whenever you need to work on Dtordered, but that's bad]

Use TO_CHAR to display a date in a given format.

You can use

TO_CHAR(<yourdate>, 'YYYYMM') = '201202' to get a year and month value.

or

EXTRACT(year from <yourdate>) = 2012 and EXTRACT(month from <yourdate>) = 2

so

SELECT Idbasket, Idshopper, TO_CHAR(Dtordered, 'MONTH DD, YYYY')
FROM bb_basket
WHERE TO_CHAR(Dtordered, 'YYYYMM')='201202';

Upvotes: 2

Related Questions