Search code examples
sql-serverconcatenationleading-zero

How to concat a calculation and use leading '0' in a view

I have a question about concat function an dleading '0' in a view. the code I used in the view is:

SELECT DATEPART(ISO_WEEK, GETDATE()) + 1 AS Week, YEAR(CURRENT_TIMESTAMP) % 2000 % 100 AS Year

This gives me the correct result. I would preferably want to have the data that is now spread over 2 columns in the same column.

Also it would be great to have a leading '0' when the result of 'Week' is a sigle digit.

Everything i tried gave an error.

Thanks for your help,

Kind regards

I expect output of (column1: '9') and (column2: '19') to be (column1: '0919')

Solution

  • You could convert your resiult to varchar, and append 00 to the start of the weeks, then take the right2 of these chars - leaving you the desired output:

     SELECT 
 RIGHT('00'+CONVERT(VARCHAR,DATEPART(ISO_WEEK, GETDATE()) + 1),2) +''+ CONVERT(VARCHAR,YEAR(CURRENT_TIMESTAMP) % 2000 % 100) [yourcol]
 FROM YOURTABLE