SQL date storing

[djomla]

Hi,

Question : I need to store date only in 'dd.mm.' format, in my case i don't need year. For example i want to have table seasons defined :

season_code  from       to
firstq             02.04.     01.06.
secq              02.06.     28.08.
thirq              29.08.     01.10.

So i can handle this from and to fields as date.

[djomla]

I can do something like
DATE_FORMAT(str_to_date(rsp.date_from , '%d.%m.'), '%d.%m.') , is this ok?

[temmokan]

Do you ask what field type to use?

To me, this is a question of functionality. If you, say, perform a lot of dates comparison (which is earlier/later), I could suggest using, say, an integer value (and store day/month as, say, 100*month +day).

If, however, more complex date manipulation/comparison is used, perhaps the built-in date type will do, where 'year' part is constant across all the values.

So the question is what operations with date values are expected ?

[djomla]

I need to find out what season is defined based on incoming date. That date must be between from and to field so i can extract season_code.

[temmokan]

I need to find out what season is defined based on incoming date. That date must be between from and to field so i can extract season_code.

The proposed integer month/day storage would make the lookup quite efficient (integer comparison only, the BETWEEN operator would be very quick).

[djomla]

Yes i think so ... and quite easy

Thank you

[temmokan]

Yes i think so ... and quite easy

Thank you

You are welcome. There were cases when I needed efficient date records storage AND comparison; if no convoluted date manipulation is assumed, I often use the YYYYMMDD-packed format.

Good luck.