Using cast in SQL with multiple column selections

Question

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

check my post below, i edited it if you are using mysql

Patrick Guimalan
– Patrick Guimalan

2012-11-08 04:20:25 +00:00
Commented Nov 8, 2012 at 4:20 — Patrick Guimalan
– Patrick Guimalan, Commented Nov 8, 2012 at 4:20

Mahmoud Gamal · Accepted Answer · 2012-11-08 04:36:18Z

8

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

edited Nov 8, 2012 at 4:36

answered Nov 8, 2012 at 4:06

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Patrick Guimalan Over a year ago

no need to convert EntryDate to varchar, i think TS said, it is already a text datatype

Mahmoud Gamal Over a year ago

@hoser What is the type of entrydate? and what RDBMS you are using MSSQL or mysql ,..?

Hoser Over a year ago

EntryDate is type Text. And I'm using MSSQL

Mahmoud Gamal Over a year ago

@Hoser - It should work fine. Try to use ADDDATE function. See my edit.

Patrick Guimalan · Accepted Answer · 2012-11-08 04:16:46Z

0

MSSQL:

$sql = "SELECT EntryDate+''+CAST(TotalTime as char(25)) as DataLine FROM TimeSheet WHERE EmployeeID='AA01'";

MySQL

$sql = "SELECT CONCAT(EntryDate,' ',CONVERT(TotalTime,char(25))) as DataLine FROM TimeSheet WHERE EmployeeID='AA01'";

edited Nov 8, 2012 at 4:16

answered Nov 8, 2012 at 4:07

Patrick Guimalan

1,01010 silver badges11 bronze badges

2 Comments

Patrick Guimalan Over a year ago

are you using MySQL or MSSQL?

Hoser Over a year ago

It still doesn't work, giving me the same 'missing operator' error unfortunately. I tried using the MySQL version just for kicks but it told me CONCAT isn't a valid method, which I figured would happen.

Collectives™ on Stack Overflow

Using cast in SQL with multiple column selections

2 Answers 2

SQL Fiddle Demo

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related