SQL Server gives syntax error

Question

I have following SQL for Microsoft SQL Server

SELECT * 
FROM tblA
WHERE CREATION_DATE BETWEEN DATE'2015-09-12' AND DATE'2015-09-15'

But this throws a syntax error:

Error: Incorrect syntax near '2015-09-12'. SQLState: S0001 ErrorCode: 102

What is wrong? I want to use ANSI literal code.

Answer 1

This is not how you cast in MS SQL Server. Instead, you should try the cast syntax:

SELECT * 
FROM   tblA
WHERE  creation_date BETWEEN CAST('2015-09-12' AS DATE) AND 
                             CAST('2015-09-15' AS DATE)

Answer 2

You can simply use this query, without adding date:

SELECT * 
FROM tblA
WHERE CREATION_DATE BETWEEN '2015-09-12' AND '2015-09-15'