none
Concat two columns RRS feed

  • Question

  • Hi,

    i have the source table data as following

    my requirement is to concat the two columns and then insert into target table.

    Expect as following output.

    but when i try to concat the two columns, i got the  following output.

    Any help would be appreciated.

    Thanks,

    Priviya

    Wednesday, September 4, 2019 11:55 AM

All replies

  • When you convert the datetime value to varchar you have to add a Format Parameter like here 120 for ODBC format

    DECLARE @date as datetime = '2019-07-03 08:27:38'
    DECLARE @id as int = 101
    
    SELECT CONVERT(varchar(9), @id) + '-' + CONVERT(varchar(20), @date, 120)

    See Convert & Cast for supported Format parameter


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 4, 2019 12:02 PM
    Moderator

  • my requirement is to concat the two columns and then insert into target table.


    Does below work?

    SELECT CONCAT (ID, '-', CONVERT(VARCHAR(23), [Date], 121)) AS IdandDate
    FROM YourTable


    Cheers
    Vaibhav
    MCSA (SQL Server 2014)

    Wednesday, September 4, 2019 12:14 PM
  • Wednesday, September 4, 2019 12:20 PM
    Answerer
  • The same i tried like this


    Ramesh. M

    Wednesday, September 4, 2019 1:30 PM
  • Hi PriviyaBelieves,

    we can also use like below script:

    DECLARE @sample TABLE(
    	Id INT,
    	[Date] DATETIME
    );
    
    INSERT INTO @sample (Id,[Date])
    VALUES (101,'2019-07-03 08:27:38.000');
    
    --Method 1
    SELECT Id,[Date],CAST(Id AS VARCHAR(10)) + '-' + CONVERT(VARCHAR(25), [Date], 21) AS IdandDate FROM @sample;
    --Method 2
    SELECT Id,[Date],CONCAT(Id,'-',CONVERT(VARCHAR(25), [Date], 21)) AS IdandDate FROM @sample;

    Note: Suggested some of the many ways that is easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Wednesday, September 4, 2019 5:22 PM
  • Hi Priviya,

     

    Please try following script.

     
    IF OBJECT_ID('SourceTable') IS NOT NULL drop table  SourceTable 
    go
    create table SourceTable
    (ID int,
    Date datetime)
    insert into SourceTable values 
    (101,'2019-07-03 08:27:38.000'),
    (102,'2019-07-03 14:47:35.000'),
    (101,'2019-07-03 14:49:03.000'),
    (102,'2019-07-03 18:48:06.000')
    
    select cast(ID as varchar(10))+'-'+ convert(varchar(30),Date,121) IDandDate
    from SourceTable
    /*
    IDandDate
    -----------------------------------------
    101-2019-07-03 08:27:38.000
    102-2019-07-03 14:47:35.000
    101-2019-07-03 14:49:03.000
    102-2019-07-03 18:48:06.000
    */
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 5, 2019 7:25 AM
  • Using sub queries:

    INSERT INTO TargetTable(IdandDate)
    VALUES(SELECT CONCAT (ID, '-', CONVERT(VARCHAR(37), [Date], 121)) AS InD FROM SourceTable)

    Goodluck


    Laborare est Orare...

    Thursday, September 5, 2019 7:48 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 9, 2019 9:40 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 23, 2019 9:45 AM