Transact-SQL string concatenation select statement error

I have a Transact-SQL stored procedure, I have written, which returns results that include a datetime field.  I am trying to use the DATEPART() function to parse out the field, so that the date that is output does not include the time (i.e...MM/DD/YYYY).  I am using the + operater to try to concatenate a forward slash, which I have in single quotes, to a column that has returned using the DATEPART() function.  I have tried using various methods, but can't get the select query to run without an error.  When I try to run (select (DATEPART(m, dateColumn) + '/'), otherColumn from Table) I get the following error message.  Syntax error converting the varchar value '/' to a column of data type int.
abastienAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

digital_thoughtsCommented:
You need to cast you DATEPART as a VARCHAR, here's another version that removes the time from a date:

CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)

This converts to a string and then back.
0
abastienAuthor Commented:
Thanks for the suggestion.  That did remove the values for the time, but now it returns a value of 00:00:00 after the date.  Is there a way to strip that out completely?
0
David ToddSenior DBACommented:
Hi,

SQL doesn't have just a date, it has a datetime. (Soon to be corrected in SQL 2008)

Stripping the time out is something that you can do in the presentation layer when formatting the field.

My preferred method of removing the time is
select dateadd( day, datediff( day, 0, getdate() ), 0 )

To show just the date and not the time as 0:00:00

select convert( varchar( 10 ), dateadd( day, datediff( day, 0, getdate() ), 0 ), 101 )

Lookup Cast and Convert in BOL
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Cheers
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abastienAuthor Commented:
Worked like a charm.  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.