CODEWITHSUNDEEP
sql-server
atc
atc

Reputation: 621

Conversion failed error in SQL for this query

I checked this error in stackoverflow but my case and error is different. Please don't make it as duplicate.

I am getting the following error

When converting date and/or time from character string in SQL

I have following query in my stored procedure. work_completion_date_inst column type is date and it stores date as following format. I'm using SQL Server 2008.

yyyy-mm-dd 
2018-04-03
2018-04-03
2018-04-14
2018-04-13

CREATE PROCEDURE [dbo].[test]
    (@P_USER_ID INT,
     @P_START_DATE DATETIME,
     @P_END_DATE DATETIME)
AS
BEGIN
    SELECT name 
    FROM table1 
    WHERE work_completion_date_inst BETWEEN @P_START_DATE AND @P_END_DATE 
      AND user = @P_USER_ID;
END;

I pass following date to the stored procedure:

P_START_DATE = 01/04/2018
P_END_DATE =  16/04/2018

As it is passing from server, in the stored procedure, it arrives as '01/04/2018 00:00:00' and '16/04/2018 00:00:00'

So I am getting that "conversion failed error".

If I pass date less than or equal to 12, the query works. Whenever it is above 12, this error popup.

For example:

select name 
from table1 
where work_completion_date_inst BETWEEN '01/04/2018 00:00:00' and '12/04/2018 00:00:00';

Can you please help me solve this ?

Upvotes: 0

Views: 69

Answers (1)

S3S
S3S

Reputation: 25112

SQL Server is parsing the day as month, and thus a conversion error. i.e. it see it as MM/DD/YYY. Pass it in as ANSI standard, YYYYMMDD and prevent this from happening, or do something else like use CONVERT() with the proper style, adjust the LANGUAGE setting, or set the DATEFORMAT for the transaction. This has to do with the language settings. Read more here from Aaron Bertrand on this issue.

select name 
from table1 
WHERE work_completion_date_inst BETWEEN '20180401' and '20180404';

To convert it as you request in the comments, you can use one of two methods:

declare @date date = '20180416'

--SQL Server 2012 onward...
select format(@date,'yyyy-dd-MM')

--Previous versions
select stuff(stuff(@date,5,3,right(@date,3)),9,2,left(right(@date,5),2))

Upvotes: 5

Related Questions