Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Unanswered: Concatenating a Date and another field

    I'm trying to Concatenate a date and another field. I've searched two solutions that work, but they aren't in a format that will work. Here are the queries that work along with their outputs

    CAST( ProcessingDate AS CHARACTER(15)) + CostCenter AS 'PDCC'
    Nov 30 2009 12:001110

    CONVERT(varchar(20), ProcessingDate) + CostCenter AS 'PDCC'
    Nov 30 2009 12:00AM1110


    But the ProcessingDate field has this format '11/30/2009' and in order for a series of excel vlookups to work properly I need the output of my query to be
    11/30/2009 1110.

    Any help would be much appreciated,
    Hank

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    you need to use convert to allow formatting with a trailing number

    CONVERT(varchar(20), ProcessingDate,103) + ' ' + CostCenter AS 'PDCC'


    Check out books online help for all of the different formats
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2009
    Posts
    4
    I can't thank you enough for your help!

    I found the other formats here and 101 did the trick:

    CAST and CONVERT (SQL Server Compact)

  4. #4
    Join Date
    Nov 2009
    Posts
    4
    After all of that the vlookups still failed. It turns out excel isn't looking up that format, or any other format, excel is using the numerical value assigned to each date. So the vlookup based on 11/30/2009 and cost center 9156 has the value 401479156.

    Is the numeric value assigned to each date unique to excel? Is concatenating in that format even possible?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    you're confusing me, because you are combining a date and a "number"

    How is that suppose the be a valid date/time?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2009
    Posts
    4
    Data is currently being imported from SQL into an excel sheet starting in column B. In column A the following formula is concatenating the data =B124&C124.

    On several other tabs the date is on the X axis and a Cost Center (CC) is on the Y axis. The following formula populates the table based on the SQL data being imported

    =IF(ISNA(VLOOKUP(P$7&$H18,Data!$A:$D,4,FALSE)),0,V LOOKUP(P$7&$H18,Data!$A:$D,4,FALSE))

    The end that I'm trying to achieve is to use a SQL Query to accomplish what =B124&C124 is currently doing, thus enabling the lookups in the other sheets to function properly without slowing down my Excel Calculations.

    My first solution was use an Array and delete column A, but most people that view the report don't know to press CTRL+SHIFT+ENTER and it's more trouble than it's worth.

    Thanks for taking the time,
    Hank

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  ptp  20091130  http://www.dbforums.com/microsoft-sql-server/1650948-concatenating-date-another-field.html#post6437328
    
    CREATE FUNCTION dbo.P2XLGlue(@pd DATETIME, @iCC INT)
    RETURNS INT
    BEGIN
       RETURN 10000 * DateDiff(day, '1899-12-30', @pd) + CASE WHEN @iCC BETWEEN 0 AND 9999 THEN @iCC ELSE 0 END
    END
    GO
    
    SELECT dbo.P2XLGlue('2009-11-30', 9156)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •