Sql Finding Month of Date

hi all,

i want to find a month from the table tableX which contains full_date, last_name ect...

full_date's format is like this : "1987.06.17 00:00:00"  

now, i write a query like this,

 SELECT full_date FROM tableX  WHERE full_date LIKE  '_____06%' this is works for JUNE's..

But i want to write a query like this SELECT full_date FROM tableX  WHERE full_date LIKE  'JUNE' ;

how can ?
LVL 1
thefirstfbliAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
well, I thought it has been said clearly enough:

SELECT full_date from tableX
where to_char(full_date, 'MONTH') LIKE 'JULY%'

will work...
now, to have good performance, you might consider creating a function-based index for that expression...
0
 
frankyteeCommented:
use the month function
SELECT full_date FROM tableX  WHERE month(full_date) = 6
0
 
Peter KwanCommented:
assume your full_date is of  VARCHAR2 type,

SELECT full_date from tableX where
to_char(to_date(full_date, 'YYYY.MM.DD HH24:MI:SS'), 'MM') = '06';
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
thefirstfbliAuthor Commented:
can not use 'June', instead of integers. without using integer 06, i want to use JUNE word in the query..
0
 
Peter KwanCommented:
Or a more simpler way:

SELECT full_date from tableX where
extract ( month from to_date(full_date, 'YYYY.MM.DD HH24:MI:SS')) = 6;
0
 
Peter KwanCommented:
If you insist to use JUNE keyword,
here is what you can do:

SELECT full_date from tableX where
to_char(to_date(full_date, 'YYYY.MM.DD HH24:MI:SS'), 'MONTH') = 'JUNE';
0
 
Peter KwanCommented:
Sorry forgot to trim the space:

SELECT full_date from tableX where
trim(to_char(to_date(full_date, 'YYYY.MM.DD HH24:MI:SS'), 'MONTH')) = 'JUNE'
0
 
thefirstfbliAuthor Commented:
should it work for this format "1987.06.17 00:00:00" .. full_date's entry is like this.Becuse it is not working. i am just trying, may there is no solution for this.

may be it will work if the entry of full_Date is like this "1987.JUNE.17 00:00:00" but know it is not working for 1987.06.17 00:00:00.. but there is no compilation fault.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the data type for your field (full_Date) ?

assuming that it is indeed datetime (or timestamp), a simplified version of pkwan's suggestion should work:
SELECT full_date from tableX
where to_char(full_date, 'MONTH') LIKE 'JUNE%'

if it's varchar, a minor change to pkwan's code should work:
SELECT full_date from tableX
where to_char(to_date(full_date, 'YYYY.MM.DD HH24:MI:SS'), 'MONTH') LIKE 'JUNE%'

0
 
thefirstfbliAuthor Commented:
full_date is DATE.. data type..

table is like this,

full_date
1987.06.17 00:00:00
1994.02.17 00:00:00
1998.03.19 00:00:00

maybe there is no sql query to do this. i just create a problem to solve it :) really it is not a problem also.

i may write

SELECT full_date FROM tableX  WHERE full_date LIKE  '_____06%'
or
SELECT full_date FROM tableX  WHERE month(full_date) = 6

instead of writing JULY in query... :(
0
 
thefirstfbliAuthor Commented:
okk.. i got.. thanks.
0
All Courses

From novice to tech pro — start learning today.