SQL Server Performance Forum – Threads Archive

append date into text

dear friends i want to do something like this update table1 set col1= col1 + ‘ ‘ + cast(col2 as varchar) here col1 is of type varchar
and col2 is of date type i am ending up in failure, any way to get the job done? waiting for reply,
thanks
Jeet

update table1 set col1 =
— first make sure you’re not concatenating with a NULL
isnull(col1, ”)
— then add a space only if there is already something in col1
+ case when col1 <> ” then ‘ ‘ else ” end
— then add the date, with an appropriate nr of characters to cover time, etc.
+ cast(col2 as varchar(30))

Why do you want to merge two columns of different datatypes?
Note that if you drop col2 then it becomes difficult to query on col1 to look for dates Madhivanan Failing to plan is Planning to fail

I’m prefer to use CONVERT converting DATETIME values – I know what the format of converted datetime value will be.
Don’t forget, col2 also could be NULL. update table1 set col1 = ISNULL(col1, ”) + ‘ ‘ + ISNULL(convert(VARCHAR(20), col2, 120), ”)
Try: DECLARE @Datetime DATETIME
SET @DateTime = NULL
PRINT ISNULL(CONVERT(VARCHAR(20), @Datetime, 120), ‘NULL value’)
SET @DateTime = ‘3/12/2006’
PRINT CONVERT(VARCHAR(20), @Datetime, 120)
PRINT CAST(@Datetime AS VARCHAR(20)) Results are:
NULL value
2006-03-12 00:00:00 – I defined format (120) how I want to see datatime
Mar 12 2006 12:00AM – accordint to server settings
Maksim

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> update table1 set col1= col1 + ‘ ‘ + cast(col2 as varchar) <hr height="1" noshade id="quote"></blockquote id="quote"> CAST( something AS VARCHAR) is a shortcut to CAST(something AS VARCHAR(1)), which might prove a little bit too narrow for holding a DATETIME value. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] If that’s not the problem, please post the error you get. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

>>CAST( something AS VARCHAR) is a shortcut to CAST(something AS VARCHAR(1)) I think the default is 30 and not 1. If it char then 1 Madhivanan Failing to plan is Planning to fail

You have to be *very* careful with CAST( …AS VARCHAR) without explicite length declaration. SQL Server is a bit inconsequent in this regard. Consider this:
DECLARE @test VARCHAR
SET @test = ‘ABC’
SELECT @test, CAST(@test AS VARCHAR) —- ——————————
A A (1 row(s) affected) That behaves exactly like I’ve mentioned. It’s the same for CHAR, btw. Now CASTing a VARCHAR to VARCHAR does not necessarily make much sense, however, one should know that. Now
DECLARE @test INT
SET @test = 123
SELECT CAST(@test AS VARCHAR) ——————————
123 (1 row(s) affected) this is not working like I would expect it. I would expect that to fail rather that simply cut-off the last two numbers. But MS decided otherwise. And actually I’ve tried
DECLARE @test DATETIME
SET @test = GETDATE()
SELECT CAST(@test AS VARCHAR) ——————————
Mar 16 2006 8:30AM (1 row(s) affected) and am a bit suprised that it isn’t cut-off. Need to keep that in mind for future use. Anyway, I would also be explicite in length when using CAST. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Cast(something to varchar) is equivalent to Cast(something to varchar(30)) if something is of the following data types 1 text 2 datetime 3 smalldatetime 4 . . I Dont know the full list [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

And this can be more specific
DECLARE @test varchar(100)
SET @test = ‘1234566666666666666666666643333333333333333333333’
SELECT CAST(@test AS VARCHAR) as Cast_To_Varchar, len(CAST(@test AS VARCHAR)) as Varchar_Len
Madhivanan Failing to plan is Planning to fail

Okay, but you see what happens when you declare a variable as VARCHAR. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Yes. Declaration makes it to have length 1. This happens only when we use cast or Convert Also, do you know what the actual length of the result returned by getdate()? When you set the mode Results in text in QA and run this ,
declare @s1 varchar(20)
declare @s2 varchar(30)
set @s1=’no’
set @s2=’the’
select @s1,@s2
select getdate() you get the following result
——————– ——————————
no the (1 row(s) affected)
——————————————————
2006-03-16 14:51:26.140 (1 row(s) affected) The number of ‘-‘ is the length of the column or varialbe which correctly matches with @s1 and @s2
But for getdate(), why does it display 54 ‘-‘s? Does it mean the maximum length is 54? Madhivanan Failing to plan is Planning to fail

Hm, never thought about that. But looks like an intesting question. I’ll see what I can dig out. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

Thanks. I hope you will give me the solution as you are DateTime Specialist [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

thanks for perfect answer
sorry for late courtesy jeet

quote:Originally posted by Adriaan update table1 set col1 =
— first make sure you’re not concatenating with a NULL
isnull(col1, ”)
— then add a space only if there is already something in col1
+ case when col1 <> ” then ‘ ‘ else ” end
— then add the date, with an appropriate nr of characters to cover time, etc.
+ cast(col2 as varchar(30))

]]>