RDLC 报告在 sql 选择查询中将文本转换为日期时间 [英] RDLC report convert a text into datetime in sql select query

问题描述

我在数据集中使用这个查询来在 rdlc 报告中显示我的数据.但它给出了错误

I am using this query in dataset to show my data in rdlc report. but it gives error

从字符串转换日期和/或时间时转换失败

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

查询:

SELECT
    NULL AS tno, 
    NULL AS accode, 
    NULL AS acname, 
    '2014-05-01' AS date, NULL AS truck, 
    NULL AS weight, 
    NULL AS quality, 
    NULL AS debit, 
    NULL AS credit, 
    SUM(debit) - SUM(credit) AS balance
FROM 
    transactions AS transactions_1
WHERE 
    (accode = @accode) AND (date < @datefrom)

UNION ALL

SELECT 
    tno, accode, acname, date, 
    truck, weight, quality, debit, credit, debit - credit AS balance
FROM 
    transactions AS transactions_1
WHERE 
    (accode = @accode) AND (date BETWEEN @datefrom AND @dateto)

在这种情况下，有人可以帮我吗?

Can anyone please help me in this case?

此处为测试目的创建表格代码.

table create code here for test purpse.

CREATE TABLE "transactions" (
"id" INT NOT NULL,
"tno" INT NOT NULL,
"pno" INT NOT NULL,
"sno" INT NOT NULL,
"crno" INT NOT NULL,
"cpno" INT NOT NULL,
"accode" INT NOT NULL,
"acname" VARCHAR(50) NOT NULL,
"date" DATE NOT NULL,
"truck" VARCHAR(50) NULL,
"weight" DECIMAL NULL,
"quality" INT NULL,
"debit" MONEY NOT NULL,
"credit" MONEY NOT NULL,
"amount" MONEY NOT NULL,
"comment" TEXT NULL,
"rate" MONEY NOT NULL,
PRIMARY KEY ("id")
);

推荐答案

试试这个.它将日期转换为日期类型.

Try this. It will cast the date to the date type.

CAST('2014-05-01' AS DATE) AS date

这篇关于RDLC 报告在 sql 选择查询中将文本转换为日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！