我的SQL查询有什么问题?

SELECT id, 
       sageaccount, 
       sageid, 
       totalwithoutvat, 
       vat, 
       total, 
       invoicedate, 
       alloweditting, 
       finished, 
       CASE WHEN isposted = 1 THEN 'Posted on ' + posteddate 
           ELSE 'Not Posted' 
       END AS Posted 
FROM Invoices 
WHERE (sageaccount = @sageaccount)

If I take the '+ posteddate' away, it works perfectly. But with the + posteddate, I get this error:

如果我把“+ posteddate”去掉，它就会完美地工作。但是对于+ posteddate，我得到了这个错误:

Conversion failed when converting datetime from character string.

从字符串转换datetime时，转换失败。

The posteddate field is usually null, unless posted is true, and the format of posteddate is definetly datetime.

posteddate字段通常为null，除非post是true，而且posteddate的格式一定是datetime。

Any ideas?

什么好主意吗?

4 个解决方案

#1

You need to convert posteddate to a varchar value in order to concatenate it with another varchar value ('Posted on ')

您需要将posteddate转换为varchar值，以便将其与另一个varchar值(' post on ')连接起来。

SELECT  id, 
        sageaccount, 
        sageid, 
        totalwithoutvat, 
        vat, total, 
        invoicedate, 
        alloweditting, 
        finished, 
        CASE WHEN isposted = 1 THEN 'Posted on ' + CONVERT(varchar(10), posteddate, 20) ELSE 'Not Posted' END AS Posted 
FROM Invoices 
WHERE (sageaccount = @sageaccount)

#2

I suspect it is complaining about you trying to add a date to a string. Depending on what platform you are using:

我怀疑它是在抱怨你试图给一个字符串添加日期。取决于您使用的平台:

1) Ensure that string concatenation can be achieved with '+'. You might have to call a function instead such as CONCAT()

1)确保字符串连接可以用'+'实现。您可能需要调用一个函数，比如CONCAT()

2) Cast the Date as a string.

将日期转换为字符串。

#3

You are adding two values with different types, so the database tries to convert one of them to the other type.

您正在添加两个具有不同类型的值，因此数据库试图将其中一个转换为另一个类型。

Convert the datetime value to a string before concatenating:

在连接之前，将datetime值转换为字符串:

SELECT
  id, sageaccount, sageid, totalwithoutvat, vat, total,
  invoicedate, alloweditting, finished,
  CASE
    WHEN isposted = 1 THEN 'Posted on ' + convert(varchar, posteddate)
    ELSE 'Not Posted'
  END AS Posted
FROM Invoices
WHERE sageaccount = @sageaccount

#4

the PostedDate must be treated as a string first before you can join it to another string. You can do a conversion this way

在将PostedDate连接到另一个字符串之前，必须先将其视为字符串。你可以用这种方法进行转换

CONVERT(varchar(10), posteddate, 101)

转换(posteddate varchar(10),101)

Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

转换语法:CONVERT (data_type [(length)]， expression [， style])

style is optional

风格是可选的

here are some samples of styles:

以下是一些款式的样品:

101 = mm/dd/yyyy ex. 01/02/1999

102 = yy.mm.dd ex. 99.02.01

102 = yy.mm。dd例99.02.01

103 = dd/mm/yyyy ex. 02/01/1999

103 = dd/mm/yyyy ex. 02/01/1999。

#1