CODEWITHSUNDEEP
t-sql
t_warsop
t_warsop

Reputation: 1270

SQL RIGHT function not working as expected

I'm trying to extract the month number from a date as a left padded string with 0's.

So, for example, from '2018-01-31' I want the string '01'.

Currently I have this:

SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS CHAR(2)), 2)

Which is returning '1' but I would have expected it to return '01' because I've provided the second argument to RIGHT as 2.

Could someone explain why this isn't working as I think it should?

Upvotes: 1

Views: 1432

Answers (2)

LukStorms
LukStorms

Reputation: 29667

You could use FORMAT instead, when you first cast the string to a DATE type.

SELECT FORMAT(CAST('2018-01-31' as DATE),'MM')

As for why that SQL with the right didn't work?

Try this SQL and notice the difference (the extra space):

SELECT quotename('0' + CAST(1 AS CHAR(2))), quotename('0' + CAST(1 AS VARCHAR(2)))

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

You need to change CHAR to VARCHAR:

SELECT RIGHT('0' + CAST(MONTH('2018-01-31') AS VARCHAR(2)), 2)

db<>fiddle demo

CHAR(2) is blank padded so you get RIGHT('01 ',2) which is '1 '.

Upvotes: 3

Related Questions