Convert dateTime to string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert dateTime to string

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2009-01-10 : 06:29:28

Hi

I'm trying to make up a view for a drop down list on a web site by concatenating a number of columns. I've done this before, but this time, I get an error 'Conversion failed when converting datetime from character string'.

So I guess I need to add in the conversion code to my select statement for the dateFrom and dateTo - can someone please show me how?

SELECT COALESCE (classType, ' ') + ' ' + COALESCE (day, ' ') + ', ' + COALESCE (timeFrom, ' ') + ' ' + COALESCE (timeTo, ' ') + ' ' + COALESCE (coachFullName, ' ')
AS class
FROM dbo.viewAllClassesAllInfo

Many thanks
Richard

Richard Law

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 06:37:20

use cast or convert to do this

as cast(day as varchar(50)).or something like that

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2009-01-10 : 06:39:22

Thanks, can you show me how that would look like with my code? - I'm not great with SQL!! :)

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-10 : 06:54:26

SELECT COALESCE (classType, ' ') + ' ' + COALESCE (convert( varchar(50),day), ' ') + ', ' + COALESCE (convert(varchar(50),timeFrom), ' ') + ' ' + COALESCE (convert(varchar(50),timeTo), ' ') + ' ' + COALESCE (coachFullName, ' ')
AS class
FROM dbo.viewAllClassesAllInfo

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-10 : 07:00:30

SELECT COALESCE (classType, ' ') + ' ' + COALESCE (cast(day as varchar(50)), ' ') + ', ' + COALESCE (cast(timeFrom as varchar(50)), ' ') + ' ' + COALESCE (cast(timeTo as varchar(50)), ' ') + ' ' + COALESCE (coachFullName, ' ')
AS class
FROM dbo.viewAllClassesAllInfo

I think This One,,,,
go with your query,,,,,

Thanks...

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2009-01-11 : 04:42:49

Thanks everyone, works a peach!

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-11 : 04:48:12

welcome...

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-11 : 23:23:07

Always Welcome.......