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

Question

Hi All
I See this Error

Msg 241, Level 16, State 1, Line 1  
Conversion failed when converting date and/or time from character string.  
  

When Use This Code ...

DATEDIFF(DAY, '2022/05/06', '2022/07/01') > 3  

How to Solve it ?
Thanks all

Answer 1

Try this :

Select DATEDIFF(DAY, '05/06/2022' , '07/01/2022')

Your system is using British/French:

--103 = dd/mm/yyyy

Answer 2

Try:

   SELECT    DATEDIFF(DAY, '20220506', '20220701') > 3  

The format YYYYMMDD without any delimiter is safe. That is, it works no matter the SET DATEFORMAT setting.

Answer 3

Hi @Shahab a

SQL Server supports many formats, most of which depend on your settings, so sometimes these settings may or may not work.
The solution to this problem is to use a date format that SQL Server supports. This format is suitable for any situation, regardless of your SQL Server language and date formatting settings.
YYYYMMDD format, you should be able to work with any version of SQL Server and any language and date formatting settings in SQL Server，As ErlandSommarskog said.

DATEDIFF(DAY,'20220506','20220701') > 3  

If you want to know more about date formats, you can click on the links below.cast-and-convert-transact-sql

Best regards,
Percy Tang

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.