在SQL中使用具有多个列选择的转换 [英] Using cast in SQL with multiple column selections

问题描述

基本上我想让这个命令工作：

  $ sql =SELECT（EntryDate + TotalTime）as DataLine FROM TimeSheet WHERE EmployeeID ='AA01'; 
  

EntryDate作为文本在数据库中，但TotalTime是一个数字。我需要把TotalTime作为一个文本，因为我注意到如果我结合两种不同类型的值，它只是空白的输出。

我知道我应该使用CAST（TotalTime为XXX），但我不知道我可以合法地将它投入（char / nchar似乎工作，字符串也不是文本）。我总是得到一个形式的错误...

 查询表达式中的语法错误（缺少运算符）'（EntryDate + CAST TotalTime as string）as DataLine FROM TimeSheet WHERE EmployeeID ='AA01''
  

我能得到一些帮助吗？非常感谢！

EDIT 我想说明的是，这并不是将EntryDate和TotalTime的值加在一起，我只想要它给我EntryDate值以及TotalTime值组合成一行，将读取类似：

10/31/12 9.25

重新编辑很抱歉没有指定之前，我使用的是MSSQL

  SELECT 
 CAST EntryDate AS VARCHAR（25））+ CAST（TotalTime AS VARCHAR（10））as DataLine 
 FROM TimeSheet 
 WHERE EmployeeID ='AA01'; 
   entrydate 是日期，int  totaltime 是一个时间，最好考虑将它们作为datetime对象，通过将它们作为日期部分添加到时间部分，具体取决于您正在使用的RDBMS。如果可能，使用 DATETIME 数据类型来表示日期和时间部分，而不是两个部分。像这样：
  SELECT DATEADD（hh，totaltime，entrydate）
 FROM TimeSheet 
 WHERE EmployeeID = AA01'; 
  
 
 
 
  SQL Fiddle Demo  
 
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. 

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...
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!

EDIT I 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:

"10/31/12 9.25"

EDIT AGAIN I'm sorry for not specifying before, I'm using MSSQL
解决方案
Try this instead:
SELECT 
  CAST(EntryDate AS VARCHAR(25)) + CAST(TotalTime AS VARCHAR(10)) as DataLine 
FROM TimeSheet 
WHERE EmployeeID = 'AA01';
However, if entrydate is a date and the int totaltime is 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 DATETIME datatype to represent both date and time parts instead of two parts. Like so:
SELECT DATEADD(hh, totaltime, entrydate)
FROM TimeSheet
WHERE EmployeeID = 'AA01';


SQL Fiddle Demo

                        
这篇关于在SQL中使用具有多个列选择的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！