Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2004
    Posts
    74

    Unanswered: Interger Concatenation

    Hey,

    Does anyone know how to do integer concatenation using sql?

    Say for instance I have 2004 and 12, I need 200412 and not 2016.

    Can this be done?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    DECLARE @x int, @y int
    SELECT @x = 2004, @y = 12
    SELECT CONVERT(int,(CONVERT(varchar(15),@x) + CONVERT(varchar(15),@y)))
    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
    Dec 2004
    Posts
    74
    Thanks for the reply,

    But what about if what I am doing is using the datepart function.

    ex.

    datepart("yy", '12/12/2004') + datepart("mm", '12/12/2004')

    I need 200412.

    Still work the same way?

    Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I could have said yes and yes and have answered the questions you aske dwith that. However, turn them into character strings and then concatenate the results.

    i.e.
    declare @year int, @month int, @result varchar(50)
    select @year = 2004, @month = 12
    select @result = convert(varchar(20),@year) + convert(varchar(20),@month
    select @result

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ya know...it's alot easier to get you the right answer when you ask the right question.

    Please don't tell me you're going to store this data in a table though

    Code:
    SELECT CONVERT(varchar(4),DATEPART(yy, '12/12/2004')) + CONVERT(varchar(2),DATEPART(mm, '12/12/2004'))
    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
    Dec 2004
    Posts
    74
    Ya, I guess the right question makes for the right answer.

    And nope, its not going in a table, just needs it to make some comparisons.

    Thanks for the help.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Spence23
    Ya, I guess the right question makes for the right answer.

    And nope, its not going in a table, just needs it to make some comparisons.

    Thanks for the help.

    Compare it to what? To data that looks like that in some other table?

    That'll scan for sure...
    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.

  8. #8
    Join Date
    Dec 2004
    Posts
    74
    Actually, I am in the process of upgrading a reporting system the uses two date inputs to bring back the correct timeframe of data, say from 11/01/2004 to 11/30/2004, but what happens now is that if you went from one year to the next say from 11/01/2004 to 02/30/2005 is crashes. So I was given the task to fix this.

    As it works now, it only uses the month part of the date to make comparsions. It only scans for data between the months of the initial date input.

    Eventually I will have to comapre full years, but for now I just needs to get it working from year to year.

    Any suggestions?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...forest for the trees, gentlemen:

    convert(char(6), cast('12/12/04' as datetime), 112)

    ...or if it already a datetime value, then just:

    convert(char(6), [YourValue], 112)

    ...but I suspect from your last post that you should not be converting this at all, and that you are better off using SQL Server's rich set of date functions than converting this to a six character string. Give us an example of the BETWEEN statement that you are using...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Dec 2004
    Posts
    74
    This is the table (should be a temp table) where the months between the dates are stored for comparsion


    create table monthspan(currentmonth int)

    declare @monthCount int
    set @monthCount = datepart(mm, @FromDate)
    while (@monthCount <= datepart(mm, @ToDate))
    BEGIN
    insert monthspan select @monthCount
    set @monthCount = @monthCount + 1
    END

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm assuming the actual data is in date form...

    You should be using DATEDIFF
    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.

  12. #12
    Join Date
    Dec 2004
    Posts
    74
    How would using DATEDIFF work if I needed to compare values to it?

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 datetime)
    GO
    
    INSERT INTO myTable99(Col2)
    SELECT '1/1/2003'  UNION ALL SELECT '2/1/2003'  UNION ALL SELECT '3/1/2003'  UNION ALL
    SELECT '4/1/2003'  UNION ALL SELECT '5/1/2003'  UNION ALL SELECT '6/1/2003'  UNION ALL
    SELECT '7/1/2003'  UNION ALL SELECT '8/1/2003'  UNION ALL SELECT '9/1/2003'  UNION ALL
    SELECT '10/1/2003' UNION ALL SELECT '11/1/2003' UNION ALL SELECT '12/1/2003' UNION ALL
    SELECT '1/1/2004'  UNION ALL SELECT '2/1/2004'  UNION ALL SELECT '3/1/2004'  UNION ALL
    SELECT '4/1/2004'  UNION ALL SELECT '5/1/2004'  UNION ALL SELECT '6/1/2004'  UNION ALL
    SELECT '7/1/2004'  UNION ALL SELECT '8/1/2004'  UNION ALL SELECT '9/1/2004'  UNION ALL
    SELECT '10/1/2004' UNION ALL SELECT '11/1/2004' UNION ALL SELECT '12/1/2004'
    GO
    
    -- Let's get the last 14 months of Data
    
    SELECT * FROM myTable99
    WHERE DATEDIFF(m,Col2,GetDate()) < 14
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    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.

Posting Permissions

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