We help IT Professionals succeed at work.

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.
Comment
Watch Question

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.

Author

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?
Senior Database Administrator
CERTIFIED EXPERT
Commented:
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

Author

Commented:
Worked like a charm.  Thanks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.