concatenate three fields with one date field

• anjaliagarwal5

  Ten Centuries

  Points: 1267

  More actions

  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.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  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!

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• whenriksen

  SSCarpal Tunnel

  Points: 4752

  More actions

  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)