CDate not working

[williadn48]

I am trying to update a field with itself plus another field in SQL Query Analyzer.

Update ECMTTConversion
set EntryDate = EntryDate + '' + Starttime
go

EntryDate has only a date in it. StartTime has only a time in it. I need to concatenate them into one field.

I keep getting:
String or binary data would be truncated.
The statement has been terminated.

 

[SQLDenis]

what datatype (including length)are the columns

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[AlexCuse]

You are working with varchar columns. YOu need to make EntryDate wide enough to hold both values concatenated (at the moment, it is not).

You should really store this in a datetime column though. If you post sample data we can help you accomplish this.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

If EntryDate and StartTime are both DateTime data types, then....

Update ECMTTConversion
set EntryDate = EntryDate + Starttime

Don't run this code twice because you'll mess up your data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

yes adding it twice will not be fun

declare @d datetime
declare @d2 datetime

select @d = convert(varchar, getdate(),112)
select @d2 = convert(varchar, getdate(),108)

select @d,@d2, @d+@d2,@d+ (@d+@d2)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[ptheriault]

What does your time data look like? Do you need to add ':'?
try this if you do?

[COLOR=blue]DECLARE[/color] @DateTime [COLOR=#FF00FF]datetime[/color]
       ,@date [COLOR=blue]char[/color](8)
       ,@time [COLOR=blue]Char[/color](6)
[COLOR=blue]SET[/color] @Date=[COLOR=red]'20070622'[/color]
[COLOR=blue]SET[/color] @Time=[COLOR=red]'094400'[/color]
[COLOR=blue]SELECT[/color] @DateTime=[COLOR=#FF00FF]CONVERT[/color]([COLOR=#FF00FF]datetime[/color], @Date + [COLOR=red]' '[/color] + [COLOR=#FF00FF]STUFF[/color]([COLOR=#FF00FF]STUFF[/color](@Time, 3, 0, [COLOR=red]':'[/color]), 6, 0, [COLOR=red]':'[/color]))
[COLOR=blue]SELECT[/color] @DateTime

- Paul

- If at first you don't succeed, find out if the loser gets anything.