0

Hello I am trying to know iF a year iS a leap-year, because i only want leap years to be inserted on the table olympics, but an error keeps coming up.

This is my attempt in SQL:

ALTER TABLE OLYMPICS
ADD CONSTRAINT LEAP_YEAR
CHECK((YEAR%4)=0);

And this is the error:

  1. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
tadman
  • 208,517
  • 23
  • 234
  • 262
Begoña Sustatxa
  • 17
  • 4
  • 2
    MySQL does not implement check constraints, so this will have no effect. Either use a trigger or don't bother. BTW, the winder olympics are in the alternative even years. – Gordon Linoff Nov 17 '17 at 20:05
  • 1
    The actual formula is `year%4=0 AND (year%100!=0 OR year%400=0)` but hey, [close enough is probably fine](https://www.theguardian.com/technology/blog/2009/jan/01/zune-firmware-mistake). – tadman Nov 17 '17 at 20:14
  • 1
    @tadman based on the table name they don’t need actual leap years anyway. There was a Summer Olympics in 1900. – Martin Smith Nov 17 '17 at 20:24
  • @MartinSmith Then this code is more for "every four years" than "every leap year". Good point. – tadman Nov 17 '17 at 20:26
  • Why is this question tagged [tag:mysql] when error message is clearly from [tag:oracle]? cf. https://stackoverflow.com/q/27987882/1695906 – Michael - sqlbot Nov 18 '17 at 01:51

1 Answers1

0

In mysql you could do something like this

  DAYOFYEAR(CONCAT(YEARVARIABLE,'-12-31'))

And then determine if it is 366 or 365 days

  Select DAYOFYEAR(CONCAT(2016,'-12-31')) 
  Result = 366  (yes leap year)

  Select DAYOFYEAR(CONCAT(2015,'-12-31')) 
  Result = 365 (no leap year)

The devided by 4 does not work, because not every year devided by 4 is a leap year

veritaS
  • 511
  • 1
  • 5
  • 23
  • 1
    This is a MySQL question, so be careful when giving advice for unrelated platforms. – tadman Nov 17 '17 at 20:25
  • mhh ok, in my head sql tag is always SQL server unless explicitly specified otherwise. In the Question that is not the case. The only indicator is the mysql tag. So to be sure I posted the SQL server answer as well, but ok. I have deleted it. – veritaS Nov 17 '17 at 20:37
  • 1
    There is `[mysql]` in the tags list. SQL is a general thing, SQL server questions are probably 20% of all SQL questions based on what I've seen. It's a fairly rare platform with Postgres and MySQL being far more popular. – tadman Nov 17 '17 at 20:39
  • @tadman MySQL yes, Postgres no. There are 76,976 questions with that tag, compared with 217,648 for SQL Server and 495,879 MySQL. – Martin Smith Nov 17 '17 at 21:09
  • @MartinSmith That is based on all-time. I bet the last year skews differently, but your point is valid. One problem is that MySQL questions do get auto-tagged as SQL Server with annoying frequency. – tadman Nov 17 '17 at 21:43
  • Not seeing a massively different skew. https://stackoverflow.com/search?q=%5Bpostgresql%5D+created%3A2017 And https://stackoverflow.com/search?q=%5Bsql-server%5D+created%3A2017 so a ratio of 2.5 to 1 which is a bit lower than the all time ratio. – Martin Smith Nov 17 '17 at 21:48