CODEWITHSUNDEEP
sql-servert-sqldatedatetime
chris c
chris c

Reputation: 331

Stored procedure datetime invalid column name

I'm trying to get date time query to work in stored procedure, but everything I try doesn't work.

Either I get errors or I get results which I'm not expecting like the query statement is not being considered.

This is the value format in the database column I'm trying to query: 

2019-10-22 02:09:04.953

If I open up a new query in SQL Server, and manually write the query it works fine, and I'm getting results after the given date:

SELECT TOP 100 *
FROM [Test].[dbo].[Order]
WHERE [CreatedOnUtc] > '10/15/2019 13:30:00.000'
ORDER BY [CreatedOnUtc]

The datatype of CreatedOnUtc is datetime

In my code I have tried numerous thing like adding culture info, using to string for formatting, and passing date time to database rather than string:

var createFromDate = createdFromUtc.Value.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture);
var createFromDate = createdFromUtc.Value.ToString("yyyy-MM-dd HH:mm:ss.fff");
var createFromDate = createdFromUtc.Value.ToString("yyyy/MM/dd HH:mm:ss.fff");
var createFromDate = createdFromUtc.Value.ToString("yyyy-MM-dd");
var createFromDate = createdFromUtc.Value.ToString("yyyy/MM/dd");

This is my stored procedure :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[OrderLoadAllPaged]
    @CreatedFromUtc NVARCHAR(MAX) = NULL,
    @CreatedToUtc NVARCHAR(MAX) = NULL
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX)

    SET NOCOUNT ON;

    SET @sql = 'SELECT TOP 100 *
                FROM [Test].[dbo].[Order] o WIT H(NOLOCK) '

    SET @sql = @sql + 'WHERE o.Deleted = 0'

    BEGIN
        SET @sql = @sql + '
        AND o.CreatedOnUtc BETWEEN "' +  CAST(@CreatedFromUtc AS nvarchar(max)) + '" AND "' +  CAST(@CreatedToUtc AS nvarchar(max)) + '"' -- + CAST(@CreatedFromUtc AS datetime) -- + Convert(datetime, @CreatedFromUtc, 126 ) --  --  -- + CAST(@CreatedFromUtc AS datetime) -- + CAST(@CreatedFromUtc AS nvarchar(max)) -- + CAST(@CreatedFromUtc AS datetime) -- + Convert(datetime, @CreatedFromUtc, 101 )  -- + CAST(@CreatedFromUtc AS nvarchar(max))  -- 
    END

    EXEC sp_executesql @sql
END

I left some comment out code in the stored procedure so you can see I tried other things. Really not sure where I'm going wrong here.

UPDATE
I changed my stored procedure to use date time, and updated my code like so but it still doesn't work.

    @CreatedFromUtc DATETIME = NULL    // new parameter declaration
BEGIN
    SET @sql = @sql + '
        AND o.CreatedOnUtc > ' +  Convert(datetime, @CreatedFromUtc, 101 ) + '' -- AND "' +  CAST(@CreatedToUtc AS nvarchar(max)) + '"' -- + CAST(@CreatedFromUtc AS datetime) -- + Convert(datetime, @CreatedFromUtc, 126 ) --  --  -- + CAST(@CreatedFromUtc AS datetime) -- + CAST(@CreatedFromUtc AS nvarchar(max)) -- + CAST(@CreatedFromUtc AS datetime) -- + Convert(datetime, @CreatedFromUtc, 101 )  -- + CAST(@CreatedFromUtc AS nvarchar(max))  -- 
    END

Also in the query I tried to use CAST(@CreatedFromUtc AS datetime) but it still didn't work.

In my C# code, I can confirm the var is a datetime.

Now I get the this error in my stack trace

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

enter image description here

Upvotes: 0

Views: 874

Answers (1)

Dale K
Dale K

Reputation: 27201

  1. Use the correct datetype i.e. date or datetime2 (datetime2 is recommended over datetime)
  2. Don't use dynamic SQL unless you absolutely have to, which is not the case here based on the information provided.

Re-writing your procedure gives:

    ALTER PROCEDURE [dbo].[OrderLoadAllPaged]
    (
      -- check that the precision matches your needs
      @CreatedFromUtc datetime2(0)
      , @CreatedToUtc datetime2(0)
    )
    AS
    BEGIN
      SET NOCOUNT ON;

      SELECT TOP 100 *
      FROM [Test].[dbo].[Order]
      -- Check the >=, < logic is correct for your purposes
      WHERE [CreatedOnUtc] >= @CreatedFromUtc
      AND [CreatedOnUtc] < @CreatedToUtc
      ORDER BY [CreatedOnUtc];

      RETURN 0;
    END

    exec [dbo].[OrderLoadAllPaged] '2019-10-15 13:30:00.000', '2019-10-16 13:30:00.000'

Upvotes: 3

Related Questions