SQL SELECT Concatenate Date and Int fields and convert result to Int

Question

I am trying to combine a date field and a number field into one in my query - I need the date to display as a number with the sequence number following.

I have tried

    SELECT CAST(movement_date AS int) + CAST(sequence_no AS int) AS MOVE

This gives the result 42312 for a date of 03/11/2015 and a sequence number of 000003. But I need the result of 42309000003 - so the date and the sequence concatenated as an INT rather than added.

Answer 1

You need to cast it append it like a string as:

SELECT CAST(CAST(movement_date AS int) as varchar(5)) + CAST(CAST(sequence_no AS int) as varchar(6)) AS MOVE

EDIT:

SELECT (CAST(CAST(movement_date AS int) as varchar(5)) +  
RIGHT('00000'+ CAST(CAST(sequence_no AS int) as varchar(6)),6)) AS [MOVE]

or you can use REPLICATE to pad the required number of 0's like this:

SELECT CAST(CAST(movement_date AS int) as varchar(5)) + REPLICATE('0',6-LEN(CAST(CAST(sequence_no AS int) as varchar(6)))) AS [MOVE]