Conversion error when converting datetime from string

Question

I am trying to create a stored procedure. The date columns use varchar data type.

I want to be able to select data using the dates as the constraints.

ALTER PROCEDURE [dbo].[spStatusReport]
     @ShipFrom VARCHAR(20) = NULL,
     @ShipTo VARCHAR(20) = NULL,
AS
    SELECT 
        LOCATION, NEXT_DEPT,
        CAST(CONVERT(VARCHAR, REQ_DATE, 104) AS DATETIME) AS REQ_DATE,
        CAST(CONVERT(VARCHAR, SCH, 104) AS DATETIME) AS SCH,
        Tool
    FROM 
        Status_Report 
    WHERE 
        (@ShipFrom IS NULL OR 
         CAST(CONVERT(VARCHAR, SCH, 104) AS DATETIME) >= CAST(CONVERT(VARCHAR, @ShipFrom, 104) AS DATETIME))
        AND (@ShipTo IS NULL OR 
             CAST(CONVERT(VARCHAR, SCH, 104) AS DATETIME) <= CAST(CONVERT(VARCHAR, @ShipTo, 104) AS DATETIME))

When I try to execute this stored procedure

exec spStatusReport '12/04/2018','NULL'

I want the user to be able to only pass one parameter either ShipFrom or ShipTO or even both but I get this error

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

you can set a default para like getdate() if SHIPTO param is null; and if SHIPFROM is null do like (getdate() - 1) — junketsu
– junketsu, Commented Dec 4, 2018 at 20:20
Your parameters are varchars instead of dates. This is making this a LOT more painful than it needs to be. Start with using the proper datatypes. — Sean Lange
– Sean Lange, Commented Dec 4, 2018 at 20:22
you can check the sample code below; to set default in case either or both params are NULL. You can check for this at start using IF statement. make sure to use BEGIN, END to encapsulate IF-statements. — junketsu
– junketsu, Commented Dec 4, 2018 at 20:26

Thom A · Accepted Answer · 2018-12-04 20:25:28Z

2

You're passing the literal string 'NULL' not the value NULL; they aren't the same. Also, you should use the ISO format for the date so it's not ambiguous. Try one of the following:

EXEC spStatusReport '20180412',NULL;

EXEC spStatusReport @ShipFrom = '20180412';

Edit: Sean Lange is right in the comments as well. Your parameters should be a date, not a varchar. Always use datatypes appropriate for your data. varchar is far from a "one size fits all" datatype.

edited Dec 4, 2018 at 20:25

answered Dec 4, 2018 at 20:21

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

2 Comments

Saahar Over a year ago

I am actually executing this using a C# web application to the exec query passes like that by default

Thom A Over a year ago

@Saahar No, C# doesn't. It definitely does not pass the literal string 'NULL' for the value NULL. This sounds like the problem is therefore your C# code, not the SQL.

junketsu · Accepted Answer · 2018-12-04 20:24:20Z

0

You can do something like this in your where clause

declare @ShipFrom varchar(20) =NULL,
@ShipTo varchar(20)= NULL

--set @ShipFrom = getdate() - 1
--set @ShipTo = getdate()

select isnull(@ShipFrom,getdate() - 1) , isnull(@ShipTo,getdate())

answered Dec 4, 2018 at 20:24

junketsu

5316 silver badges18 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-12-04 20:56:53Z

I strongly recommend that you pass date/time values as date/datetime or a related data type.

Then, don't convert to strings to remove a time component. SQL Server has better mechanisms. And don't convert strings to strings; it just doesn't make sense.

You can also use try_convert() where appropriate:

ALTER PROCEDURE [dbo].[spStatusReport] (
     @ShipFrom VARCHAR(20) = NULL,
     @ShipTo VARCHAR(20) = NULL
) AS
BEGIN
    SELECT 
        LOCATION, NEXT_DEPT,
        CAST(CAST(REQ_DATE AS DATE) AS DATETIME)  AS REQ_DATE,
        CAST(CAST(SCH AS DATE) AS DATETIME) AS DATETIME) AS SCH,
        Tool
    FROM Status_Report 
    WHERE (@ShipFrom IS NULL OR 
           TRY_CONVERT(DATE, SCH, 104  >= TRY_CONVERT(DTAE, @ShipFrom, 104)
          ) AND
          (@ShipTo IS NULL OR 
           TRY_CONVERT(DATE, SCH, 104) <= TRY_CONVERT(DATE, @ShipTo, 104) 
         );
END;

Collectives™ on Stack Overflow

Conversion error when converting datetime from string

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related