how to select year on CHAR, ISO date 2

[iceman42]

I have a query and rather then run through five years to get one year I would rather take care of that in a query. So how would I do that? The date format is ISO which is YYYY-MM-DD and I only want 2005.

 

[ddiamond]

You could use the year function which takes either an iso string representation of the date or an actual date and returns the year.

select <field-list>
from <tables/joins>
where year(iso_date_string) = 2005

 

[gregsimpson]

Iceman,

have you tried using a between or a > & <.

select ......
where ice_date between '2005-01-01' and '2005-12-31'

Could do a similar thing with < & >. I guess you could alsways cast to char and then substr the resultant, though I don't imagine that would be very efficient.

Cheers
Greg

 

[gregsimpson]

ddiamond,

nice one.

Greg

 

[ddiamond]

Greg,

Thanks. Actually your solution may be more efficient if there is an index on iso_date.

- Dan

 

[iceman42]

Thanks Greg and Dan, I'm actually going to need both of those queries so that was awesome to see both.

Dan

 

[iceman42]

Just to let both of you know, there is a 20% faster response in gregsimpson's query, both gave me over a 50% faster response then my original query.