1

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.

Emma
  • 577
  • 1
  • 13
  • 27
  • Can you add the reason why you are trying to do this, as it seems a bit hacky and there could be a better solution than casting/converting and appending strings. – Tanner Nov 03 '15 at 11:15
  • Hi Tanner, i'm trying to get a unique id field - using SQL 2000 and row_number() isn't compatible, I have read temp tables can do this job but I don't have any experience of them and this seemed the easiest solution. – Emma Nov 03 '15 at 11:26
  • so sequence number can always have leading zeros 5 or it can be randomly? – Pedram Nov 03 '15 at 11:36
  • sequence number seems to be a unique id for that date - so today so far we have 000001, 000002,000003,000004. yesterday's sequence ended on 000027 then it reset to 1 the next day if that makes sense. – Emma Nov 03 '15 at 11:39
  • then I think we should avoid using RIGHT with hard coded `00000` - this is what I'm thinking. – Pedram Nov 03 '15 at 11:41

3 Answers3

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]
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • Thanks for your help. This is giving the result 423093 - so missing the leading zero's from the sequence number for some reason. I need the result as 42309000003. (The sequence number is always a 6 digit number). – Emma Nov 03 '15 at 11:17
  • @Emma to pad with zeroes: http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008 – Tanner Nov 03 '15 at 11:18
  • will this just change the format in the select query ? I don't want to affect the table itself. – Emma Nov 03 '15 at 11:23
  • 1
    @Emma:- No it will not affect the table. See the updated query! – Rahul Tripathi Nov 03 '15 at 11:32
  • Thank You - Just trying it now ! – Emma Nov 03 '15 at 11:36
  • Incorrect syntax near AS - shouldn't the AS be at the end ! ? – Emma Nov 03 '15 at 11:37
  • 1
    @Emma:- `SELECT (CAST(CAST(movement_date AS int) as varchar(5)) + RIGHT('00000'+ CAST(CAST(sequence_no AS int) as varchar(6)),6)) AS [MOVE]` – Rahul Tripathi Nov 03 '15 at 11:38
1

This is how you should do it.

SELECT 
{fn concat(CAST(CAST(movement_date AS int) AS varchar), CAST(CAST(sequence_no AS int) AS varchar))} AS MOVE FROM table_name

or

SELECT 
CONCAT(CAST(CAST(movement_date AS int) AS varchar), CAST(CAST(sequence_no AS int) AS varchar)) AS MOVE FROM table_name

If you still want to select as int or any other number type, you just encapsulate everything into CAST(value AS anyNumberType).

EDIT: If you want to keep leading zeros in "sequence_no", you shouldn't CAST as int, because that is where leading zeros are removed. Just cast into something else (varchar maybe). 

SELECT 
{fn concat(CAST(CAST(movement_date AS int) AS varchar), CAST(sequence_no AS varchar))} AS MOVE FROM table_name

or 

SELECT 
CONCAT(CAST(CAST(movement_date AS int) AS varchar), CAST(sequence_no AS varchar)) AS MOVE FROM table_name

Also, you can use "+" sign to concatenate non-numeric values, but I prefer functions :)

Amel
  • 708
  • 6
  • 17
  • Thanks Proleev - This is also giving the result 423093 - so missing the leading zero's from the sequence number for some reason. I need the result as 42309000003. (The sequence number in the table is always a 6 digit number with leading zero's). – Emma Nov 03 '15 at 11:24
1
SELECT CAST(movement_date AS int) + CAST(sequence_no AS int) AS MOVE

If you want to append your movemen_date with sequence_no then don’t cast them as int. 

Cast tem as nvarchar.

SELECT CAST(movement_date AS nvarchar(max)) + CAST(sequence_no AS nvarchar(max)) AS MOVE
Mike Clark
  • 1,860
  • 14
  • 21