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

Question

This is a SQL sub query and this is giving the error as

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

I've tried replacing datetime with datetime2 but it doesn't work.

declare @cols as NVARCHAR(max)
, @cols2 as NVARCHAR(max)
, @query as NVARCHAR(max) select
  @cols = STUFF(
    (
      select
        ',' + QUOTENAME(ProductOptionName)
      from
        OrderProductVariantOption [NL]
        inner join OrderProductVariant [NL] on
          OrderProductVariant.Id = OrderProductVariantOption.OrderProductVariantId
        where
          (
            OrderProductVariant.AcceptedById = '[Business|0]'
            or OrderProductVariant.MRefId = '[Business|0]'
          )
          and [OrderProductVariant.MarketplaceGroupId=Marketplace]
          and OrderProductVariant.DateCreated >= cast(cast('[startdate]' as date) as datetime2)
          and OrderProductVariant.DateCreated < cast(cast('[enddate]' as date) as datetime2)
        group by
          ProductOptionName
        order by
          ProductOptionName for XML path('')
          , TYPE
    )
    . value('.', 'NVARCHAR(MAX)')
    , 1
    , 1
    , ''
  )

Answer 1

1. Remove [NL] because [NL] is used multiple times.

2. Maybe the OrderProductVariant.DateCreated date formate is different. so also correct it's format.

   and cast(OrderProductVariant.DateCreated as date) >= cast([startdate] as date)

Finally, your code would be like this :

 declare @cols as NVARCHAR(max)
, @cols2 as NVARCHAR(max)
, @query as NVARCHAR(max) select
  @cols = STUFF(
    (
      select
        ',' + QUOTENAME(ProductOptionName)
      from
        OrderProductVariantOption 
        inner join OrderProductVariant on
          OrderProductVariant.Id = OrderProductVariantOption.OrderProductVariantId
        where
          (
            OrderProductVariant.AcceptedById = '[Business|0]'
            or OrderProductVariant.MRefId = '[Business|0]'
          )
          and [OrderProductVariant.MarketplaceGroupId=Marketplace]
        and cast(OrderProductVariant.DateCreated as date) >= cast([startdate] as date)
        and cast(OrderProductVariant.DateCreated as date) < cast([enddate] as date)
        group by
          ProductOptionName
        order by
          ProductOptionName for XML path('')
          , TYPE
    )
    . value('.', 'NVARCHAR(MAX)')
    , 1
    , 1
    , ''
  )

Answer 2

'[startdate]' and '[enddate]' are literal string values - you are not attempting to convert the value inside a column named startdate (or enddate) to a date, but the actual string value: [startDate] (or [enddate]).
Naturally, these strings can't be converted to date values.

Also, you have another problem in your query - you are attempting to use the same aliases for both tables in the join - that will lead to the following error:

The correlation name 'NL' is specified multiple times in a FROM clause.

Answer 3

You quoted [startdate] and [enddate]. Thus, they are the constant string value [startdate] and [enddate] and no more a field name. Try

          and OrderProductVariant.DateCreated >= cast([startdate] as date)
          and OrderProductVariant.DateCreated < cast([enddate] as date)

And you should use the CONVERT function instead of CAST as you can set the date format as a parameter.

Answer 4

use this:

and OrderProductVariant.DateCreated >= Convert(datetime,[startdate])
and OrderProductVariant.DateCreated < Convert(datetime,[enddate])

Answer 5

You need to update your query as there is some syntax issue in your query. In sql server use [] to declare your column name. '' is use to declare string value. So remove ' from your date column.

Also need to remove table alias [NL] since you declare same alias for both tables and using table name directly in your query.

On top your select query start from the declare statement, which further give error. Write your query from the next line.

declare @cols as NVARCHAR(max)
, @cols2 as NVARCHAR(max)
, @query as NVARCHAR(max) 

select
  @cols = STUFF(
    (
      select
        ',' + QUOTENAME(ProductOptionName)
      from
        OrderProductVariantOption      ---- remove [NL] since you are using table name directly in your query
        inner join OrderProductVariant  on
          OrderProductVariant.Id = OrderProductVariantOption.OrderProductVariantId
        where
          (
            OrderProductVariant.AcceptedById = '[Business|0]'  --- check over there too
            or OrderProductVariant.MRefId = '[Business|0]'
          )
          and [OrderProductVariant.MarketplaceGroupId=Marketplace]
          and OrderProductVariant.DateCreated >= cast([startdate] as date) 
          and OrderProductVariant.DateCreated < cast([enddate] as date) 
        group by
          ProductOptionName
        order by
          ProductOptionName for XML path('')
          , TYPE
    )
    . value('.', 'NVARCHAR(MAX)')
    , 1
    , 1
    , ''
  )