CODEWITHSUNDEEP
sqlsql-server-2008datetime
Silentbob
Silentbob

Reputation: 3065

sql combining two columns one of which is a datetime

I have the following sql code (ms sql server 2008)

select (analysisno + ' ' + '-' + ' ' + description + ' ' + '-' + ' ' + formdate) as columnA

from Old_Analysis_Data order by formdate

I get the following error Conversion failed when converting date and/or time from character string.

AnalysisNo is a varchar(10) description is a varchar(500) formdate is a datetime (not my table, its an old one)

Any ideas, as cant find an answer on google.

Upvotes: 2

Views: 1412

Answers (3)

John Woo
John Woo

Reputation: 263683

Instead of concatenating formdate directly, convert it to string first,

convert(varchar(15), formdate, 103)

which gives you the following format

dd/MM/yyyy

Upvotes: 0

marceljg
marceljg

Reputation: 1067

Convert the time to a string using Convert before concatenation:

SELECT ( analysisno + ' ' + '-' + ' ' + description + ' ' + '-' + ' ' 
     + CONVERT(VARCHAR(20), formdate, 100) ) AS columnA 
FROM 
     Old_Analysis_Data 
ORDER BY 
     formdate

In this case, 100 is a style that sets datestamp format to mon dd yyyy hh:miAM (or PM) as an example

See http://www.w3schools.com/sql/func_convert.asp

Upvotes: 1

Andrey Gordeev
Andrey Gordeev

Reputation: 32449

Try this:

select (analysisno + ' - ' + description + ' - ' 
+ convert(varchar(100),formdate)) as columnA
from Old_Analysis_Data order by formdate

Upvotes: 0

Related Questions