Conversion failed error in SQL for this query

Question

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 ?

There isn't anything about the number 12 that gives you a clue? — Tab Alleman
– Tab Alleman, Commented Apr 17, 2018 at 17:57

S3S · Accepted Answer · 2018-04-17 19:19:54Z

5

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))

edited Apr 17, 2018 at 19:19

answered Apr 17, 2018 at 17:38

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

atc Over a year ago

I tried following but not working "select convert(datetime,'16/04/2018 00:00:00',21)"

S3S Over a year ago

That's not the way you are passing it in. You need to use 103 as the style for that form. Also, you don't need to convert it to a DATETIME since your columns are of type DATE. select convert(date,'16/04/2018 00:00:00',103)

atc Over a year ago

If I want the output as "2018-16-04" what should I do, I changed to 106 from 103 but shows "2018-04-16"

S3S Over a year ago

Use the exact same code in the select statmentselect name, work_completition_date = convert(varchar,work_completition_date,103). Why would you use 106? This doesn't have any slashes...

S3S Over a year ago

My bad, I see what you mean. See the edit in my answer.

Collectives™ on Stack Overflow

Conversion failed error in SQL for this query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related