在具有多个列选择的 SQL 中使用强制转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13282223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using cast in SQL with multiple column selections
提问by Hoser
Basically I'd like to get this command to work:
基本上我想让这个命令工作：
$sql = "SELECT (EntryDate + TotalTime) as DataLine FROM TimeSheet WHERE EmployeeID='AA01'";
EntryDate is in the database as a text, but TotalTime is a Number. I need to cast TotalTime as a text, because I've noticed if I combine two differing types of values, it just blanks out the output.
EntryDate 作为文本在数据库中，但 TotalTime 是数字。我需要将 TotalTime 转换为文本，因为我注意到如果我组合两种不同类型的值，它只会使输出空白。
I know I'm supposed to use CAST(TotalTime as XXX) but I'm not sure what I can legally cast it to (char/nchar doesn't seem to work, neither does string nor text). I always get an error of the form...
我知道我应该使用 CAST(TotalTime as XXX) 但我不确定我可以合法地将它转换为什么（char/nchar 似乎不起作用，字符串和文本也不起作用）。我总是收到表格错误...
Syntax error (missing operator) in query expression '(EntryDate + CAST(TotalTime as string) as DataLine FROM TimeSheet WHERE EmployeeID='AA01''
Could I get some help? Thank you!
我能得到一些帮助吗？谢谢！
EDITI would like to note this isn't intended to add together the values of EntryDate and TotalTime together to produce a single value. I simply want it to give me the EntryDate value as well as the TotalTime value combined into a single line that would read something like:
编辑我想指出这并不是为了将 EntryDate 和 TotalTime 的值加在一起以产生单个值。我只是想让它给我 EntryDate 值以及 TotalTime 值组合成一行，内容如下：
"10/31/12 9.25"
“2012 年 10 月 31 日 9.25”
EDIT AGAINI'm sorry for not specifying before, I'm using MSSQL
再次编辑很抱歉之前没有指定，我正在使用 MSSQL
回答by Mahmoud Gamal
Try this instead:
试试这个：
SELECT
CAST(EntryDate AS VARCHAR(25)) + CAST(TotalTime AS VARCHAR(10)) as DataLine
FROM TimeSheet
WHERE EmployeeID = 'AA01';
However, if entrydateis a date and the int totaltimeis a time, it may be better to consider converting them as a datetime object by adding them as a date part to the time part depending on the RDBMS you are using. And if possible use a DATETIMEdatatype to represent both date and time parts instead of two parts. Like so:
但是，如果entrydate是日期而 inttotaltime是时间，则最好考虑将它们转换为日期时间对象，方法是将它们作为日期部分添加到时间部分，具体取决于您使用的 RDBMS。如果可能，请使用DATETIME数据类型来表示日期和时间部分而不是两部分。像这样：
SELECT DATEADD(hh, totaltime, entrydate)
FROM TimeSheet
WHERE EmployeeID = 'AA01';
SQL Fiddle Demo
SQL 小提琴演示
回答by patrick choi
MSSQL:
MSSQL：
$sql = "SELECT EntryDate+''+CAST(TotalTime as char(25)) as DataLine FROM TimeSheet WHERE EmployeeID='AA01'";
MySQL
MySQL
$sql = "SELECT CONCAT(EntryDate,' ',CONVERT(TotalTime,char(25))) as DataLine FROM TimeSheet WHERE EmployeeID='AA01'";
