-2

I have a field appointment_date and appointment_time and datatype is date and time respectively. The table name is appointment. What our teacher wanted was to be able to add appointment ONLY if it is greater than or equal to the current_date or current_time. I am quite confused with how getdate(), sysdate(), dateadd() or any DATE functions work. Hope you can help me out. Thanks.

Utsav
  • 7,914
  • 2
  • 17
  • 38
Connie Varley
  • 33
  • 4

2 Answers2

0

If you're getting lost with time/date functions, there is great documentation with MySQL on the net, check it out here : Date and time functions

Given the fact that MySQL doesn't have a conditional INSERT statement, I would advise doing all sorts of checks on the values you want to insert before doing a simple insert without conditional check.

Adrien Brunelat
  • 4,492
  • 4
  • 29
  • 42
  • The syntax `:varName` is JCBD Template' syntax, it stads for the values you're trying to assign. This should be adapted to the way you are actually inserting values into youre SQL query. – Adrien Brunelat Apr 04 '16 at 13:34
  • Thanks for the suggestion. How do you insert these in MySQL if you don't mind? – Connie Varley Apr 04 '16 at 13:39
  • 1
    In mysql the on clause can only have `on duplicate key update`, you cannot do such checks there. – Shadow Apr 04 '16 at 13:40
  • Yep, you're totally right. That completely voids my suggestion. Back to investigation! – Adrien Brunelat Apr 04 '16 at 13:44
  • Well the thing is, from what I see in my everyday work, we do that kind of check BEFORE doing the insert. The reason being, I guess, that MySQL doesn't really have a conditional INSERT. – Adrien Brunelat Apr 04 '16 at 13:47
0

In sql you could enforce such logic using a check constraint, unfortunately, mysql does not implement this type of constraint just yet. This means, that you either have to check the condition in the application itself before calling an insert statement or you have to create a stored procedure to achieve the expected results.

In both solution the logic is simple: with an if conditional statement you check if the value you would like to insert is larger than current date or time (it is actually quite important to which you compare it to).

The 3rd solution is to create a before insert trigger and raise an sql error message from it with signal to prevent the insertion of the record.

Shadow
  • 33,525
  • 10
  • 51
  • 64