Post reply

concatenate three fields with one date field

  • March 31, 2017 at 10:44 am

    #401871

    Hi All,

    I have a date field in my table. I need to concatenate this date field with a varchat field. My varchar field has this value in it

    Col1       col2          DateTransfer

    125678    8654323          2016-08-30 00:00:00.000

    I need to concatnate all these three fields together to get ane column called col5

    I tried doing this

    select col1 + col2 + DateTransfer
       from tableA

    I am getting an error saying

    Msg 241, Level 16, State 1, Line 8
    Conversion failed when converting date and/or time from character string.

    then I tried to convert dateTransfer to varchar then the format of the date is changed. I need this output without  any format change and last .000 to be dropped off

    Col5

    12567886543232016-08-30 00:00:00

    I don't want the last .000 in the concatenated value.

    any help will be appreciated.

  • March 31, 2017 at 10:55 am

    #1936024

    you have to convert all your fields to the same explicit data type.
    here's a full example, based on your copy/paste:
    note my CTE, which represents your data, has three different datatypes, all of which will throw potential errors when you concatenate, instead of actually add them
    also, there is an additional parameter to the convert function, so you can decide on the specific format.

    WITH MySampleData(Col1,col2,DateTransfer)
    AS
    (
    SELECT CONVERT(int,125678), '8654323',CONVERT(datetime,'2016-08-30 00:00:00.000')
    )
    SELECT CONVERT(varchar(30),Col1) + '-' + col2 + '-'+ CONVERT(varchar(30),DateTransfer,120) AS Results
    FROM MySampleData
    --Results
    --125678-8654323-2016-08-30 00:00:00

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 31, 2017 at 11:37 am

    #1936042

    SELECT CAST(col1 AS varchar(30)) + CAST(col2 AS varchar(30)) + CONVERT(varchar(19), DateTransfer, 120)

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • April 11, 2017 at 2:17 pm

    #1937720

    Use the Concat function, all of the inputs are converted to character types.  I cast the date as a datetime2(0) to get rid of the milliseconds.

    DECLARE @Col1 int = 125678, @Col2 INT = 8654323, @DateTransfer DATETIME = GETDATE();

    SELECT Col1 = @Col1,
    Col2 = @Col2,
    DateTransfer = @DateTransfer,
    Answer = CONCAT(@Col1,' ',@Col2,' ',CAST(@DateTransfer AS DATETIME2(0)))

    Wes
    (A solid design is always preferable to a creative workaround)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply