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.
Who is Participating?
David ToddConnect With a Mentor Senior DBACommented:

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

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


This converts to a string and then back.
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?
abastienAuthor Commented:
Worked like a charm.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.