Search code examples
sql-serversql-server-2019

Converting a string to a datetime in MS SQL Server showing 19th Century date

When I ran the below query in MS SQL Server 2019, it returned the correct date value:

SELECT CONVERT(DATETIME,'13/12/2019',103)

Output:

2019-12-13 00:00:00.000

But when I ran the below code block with the same query, I am not getting correct date value in the output. I am getting a 19th century date value.

DECLARE @BEFOREDATE varchar(30)

DECLARE @SqlStatment VARCHAR(1000)

BEGIN

   SET @BEFOREDATE = '13/12/2019'

   SET @SqlStatment = 'select CONVERT(DATETIME,' + @BEFOREDATE + ',103)'

   EXECUTE( @SqlStatment )

END

Output:

1900-01-01 00:00:00.000

Please help to resolve this issue.

My SQL Server database has the below specifications: enter image description here

Solution

  • The problem is injection, and bad injection at that. The best way to debug "dynamic" SQL is to PRINT/SELECT the dynamic statement. If we do that, we get the following:

    select CONVERT(DATETIME,01/03/2023,103)

    So, this means you want to convert the integer expression 01/03/2023 to a datetime, so that's 1/3/2023->0/2023->0, and 0 as a datetime is 1900-01-01.

    Coincidentally, if you used date, which it appears you should be using, you would have got a "better" error that would have likely pointed you to the real problem:

    Explicit conversion from data type int to date is not allowed.

    As I stated though, the problem is injection, you should be parametrising the value. You can't do that with EXEC(<Variable>) syntax, and I actually stronly recommend against its use because of that; it promotes injection because it can't be parametrised. Instead use sys.sp_executesql and pass the variable to it as a parameter:

    DECLARE @BeforeDate varchar(30);
DECLARE @SqlStatement nvarchar(MAX); --varchar is not a valid datatype for `sys.sp_exutesql`.

SET @BEFOREDATE = '01/03/2023';
SET @SqlStatement = 'SELECT CONVERT(datetime,@BeforeDate,103);';

EXEC sys.sp_executesql @SqlStatement, N'@BEFOREDATE varchar(30)', @BEFOREDATE;

    Of course, this makes the whole "dynamic" statement pointless, as it's no longer dynamic.