Date Value Not Populating in Native SQL for SAP Order Insertion

I think you need to put a colon (before variable as below:

EXEC SQL.
   INSERT INTO order VALUES('2', :sy-datum)
ENDEXEC.

I would also suggest you to use OpenSQL instead of Native SQL here. Native SQL is used when you try to use any features that are database specific.

The query you have used is very generic and not exclusive for backend database.