How to retrieve DATETIME fields from RDS database on S3 site via API Gateway and Lambda function

Question

I have a public site hosted through S3. It is connected to an RDS/MySQL database via an API gateway and a Lambda function. This has worked very well for me so far. However, I am unable to select any DATETIME fields from the database. Every time I have a DATETIME field in the SELECT clause of a SQL statement, I receive an internal server error. This only occurs with DATETIME fields. This also only occurs in the SELECT clause. I have been able to use DATETIME fields in ORDER BY clauses with no issue. How can I get around this and retrieve these fields?

I am also only concerned with the date part of these fields. If I can't get the entire field, is there any way I could extract just the date part?

Answer

Please make sure that you are using the correct format when making DATETIME queries. The correct format for DATETIME is stated below:
DATETIME - format: YYYY-MM-DD HH:MI:SS 
Additional, please make sure that you are inputting correct values for DATETIME. In SQL, the DATETIME data type supports a value range between 1753-01-01 and 9999-12-31.
 
More can be found on the following page: https://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/chap-sql-server-aurora-mysql.sql.datatypes.html 
Please let us know if we can further assist you!

How to retrieve DATETIME fields from RDS database on S3 site via API Gateway and Lambda function

Contenido relevante