SQL Error trying to get a BETWEEN in a DateTime2 Column-CodePudding

I am trying to filter a SQL database using BETWEEN:

SELECT * 
FROM RegistroCaja 
WHERE Fecha BETWEEN CONVERT(datetime2, '15/12/2021 08:54:33')
                AND CONVERT(datetime2, '17/12/2021 08:54:34')

But I get this error:

Conversion failed when converting date and/or time from character string.

The Fecha column is of type DateTime2, if someone can help me please!

CodePudding user response：

Your dates are not formatted for valid datetime2 parsing:

See https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15 for more information. But switch the day with the month and you should be able to do the conversion.

CodePudding user response：

Most likely format problem, try to use:

SELECT * 
    From RegistroCaja 
    WHERE Fecha BETWEEN
        CONVERT(datetime2 , '2021-12-15 08:54:33')
        and CONVERT(datetime2, '2021-12-17 08:54:34')

CodePudding user response：

Try to pass the style as the third argument to CONVERT function.

SELECT * 
    From RegistroCaja 
    WHERE Fecha BETWEEN
        CONVERT(datetime2 , '15/12/2021 08:54:33', 131)
        and CONVERT(datetime2, '17/12/2021 08:54:34', 131)