help with concatanate of columns 2

[baboudi]

Hi there,

wandering if anyone can help, i need to combine several columns in sql but can't get around it. The '+' is actually adding the values and I tried convert the date to char or varchar but keep having:

Syntax error converting the varchar value '04/02/2004' to a column of data type int.

eg
x y date
1234 00 2004-02-04 16:47:26.000


I need to have the following 1234 00 20040204164726000 (i added the spaces just for clarity)

as a second set of data, when i combine the values, i need to have a total of 8 digits for the first set of values (column x)

for example if x contain 4 digits, my resultant value need to be:
12340000 00 20040204164726000 (spaces added for clarity)

if x contained 5 digits:
12345000 00 20040204164726000 (spaces added for clarity)

many thanks
Benoit

 

[bborissov]

SELECT LEFT(CAST(x as varchar(8))+'00000000', 8)+
       CAST(y as varchar(2))                    +
       REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(200),date,121),'.',''),':',''),'-',''),' ','')
       FROM YourTable

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[baboudi]

it worked perfectly!
Thanks a lot for your quick help