select from Date data type table column

[apnea]

I like to select from a Date data type column years and months. The contents of this column have a specific
format: e.g 7/22/98 8:00:00 AM. When i try to make a qery e.g SELECT * FROM TASK1_TABLE WHERE Task_Table1.START_DATE
LIKE '%22/98%' the result is an empty table. How can i select specific months and years?

Thanks for your help

 

[carp]

It will probably depend a lot on the RDBMS you are using.
If you are using Oracle, you could try something like:

SELECT *
FROM task1_table
WHERE to_char(start_date,'MON YY') = 'MAR 01';

 

[apnea]

Yes carp im using oracle. Your query returns an empty table, i think the problem is that the column includes olso the time: 7/22/98 8:00:00 AM. I dont now, can you suggest something?
Thanks

 

[krkX]

The way i do it:
Lets say i want all of month 3 of this year..

SELECT [date] FROM

WHERE CONVERT(CHAR(10), [date], 112) LIKE '200103%'

(add this to sort properly: CONVERT(CHAR(10), [date], 112) )

 

[apnea]

krkX do you use oracle?

 

[carp]

If you are using my query verbatim, are there any rows that have a start_date in March 2001? If not, that would explain the empty data set. On the other hand, if you DO have such data available, it may be the way your database is set up to look at dates. In that case, change the query to look like:

SELECT *
FROM task1_table
WHERE to_char(start_date,'MON RR') = 'MAR 01';

 

[karluk]

Both of Carp's suggestions should work. I can query my own data using his selection logic.

I'm not sure why your queries are returning empty result sets. One possibility is that capitalization is important in this query. "To_char(start_date,'mon rr')" returns the months in lower case, so "where to_char(start_date,'mon rr') = 'MAR 01'" will indeed always fail to return any rows.

 

[Nebuchednezzar]

Be sure the field you are selecting from is in fact a date datatype (describe the table)...if it is actually stored as a char or varchar, the query would need to be written differently.