0

I want to save in a database table the month and day part of a date. That will be fixed regardless of the actual year so I don't need to save the year as well.
I could define a type like: char(5) and save it as: 01-01 but this would not "protect" from garbage strings.

Is there a way to define it better? E.g. does an enum for month and day make sense? (though still something like 02-30 could be defined which is of course meaningless)

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Jim
  • 18,826
  • 34
  • 135
  • 254
  • You could use two fields instead of one. You will have more control over them – JCalcines Aug 12 '14 at 07:57
  • @JCalcines:But what type?I would have the same problem but in 2 fields (e.g. if I defined them as char or int) – Jim Aug 12 '14 at 08:14
  • You can use `int` for both of them. It's easier to check if month is between 1 and 12 and day between 1 and 31. Also you can extract the month and day from the date with `EXTRACT(MONTH from '2014/08/12')` – JCalcines Aug 12 '14 at 08:17
  • @JCalcines:But the column can still be updated with the wrong values. You assume that the table will be updated only via e.g. a UI – Jim Aug 12 '14 at 08:31
  • Ok, I'm starting to see your point, but what happens with Februaty 29th. Is this right or wrong? – JCalcines Aug 12 '14 at 08:57
  • @JCalcines:If I can get away with meaningful dates I am ok with February to have an extra day. I mean I am looking for a reasonable approach not solve all the corner cases that depend on the year – Jim Aug 12 '14 at 10:54
  • if you store as 2 small ints, can you add a check constraint like this: create table t1(c1 int1 check(c1 between 1 and 12), c2 int2 check (c2 between 1 and 31), check((c1 in (4,6,9,11) and c2 < 31) or (c1 = 2 and c2 < 30) or (c1 in (1,3,5,7,8,10,12)))) – mc110 Aug 12 '14 at 14:32
  • ah - mysql ignores check constraints, but http://stackoverflow.com/questions/14247655/mysql-check-constraint shows how to replace them with equivalent triggers – mc110 Aug 12 '14 at 15:10

1 Answers1

-1
select to_char(to_date('2011-01-17', 'yyyy-mm-dd'),'dd-mm') from dual;

17-01

It`s written in Oracle, you can use logic from the Query for SQL.

Illidanek
  • 996
  • 1
  • 18
  • 32
Asad Mirza
  • 1
  • 4
  • My question is about **storing** the value.Not about how to retrieve the month part of a date. – Jim Aug 12 '14 at 13:09