Skip to main content

Have a field InvoicedDate. I want to get the last two digits of the year of the InvoiceDate value. So for example, if InvoiceDate contains “2/15/2021” I want to get the result “21”. This is what I’ve tried:


RIGHT(YEAR(InvoicedDate),2)


Looks okay to me, but it returns #ERROR. I suspect it’s a typing problem, that is, YEAR() returns a number, but RIGHT() can only be used with a string. But I can’t see how to coerce the number to be a string. I’ve reread the functions reference but can’t find a NUMSTR() function or anything like that. What am I missing?


William

Hi William. Just want to make sure that InvoicedDate is a date field (you put 2/15/2021 in quotes).


If it is a date field, then the error is coming from RIGHT. Try RIGHT(YEAR(InvoicedDate)&"",2). The & concatenation returns everything as a string.

The ideal way to do this would be to use the DATE_FORMAT function like this:


DATETIME_FORMAT(InvoicedDate,'YY')


You can find a list of supported format specifiers here:


The ideal way to do this would be to use the DATE_FORMAT function like this:


DATETIME_FORMAT(InvoicedDate,'YY')


You can find a list of supported format specifiers here:




I was just about to suggest the same. Beat me to it, lol

Hi William. Just want to make sure that InvoicedDate is a date field (you put 2/15/2021 in quotes).


If it is a date field, then the error is coming from RIGHT. Try RIGHT(YEAR(InvoicedDate)&"",2). The & concatenation returns everything as a string.


Thanks. Yes, InvoiceDate is a date field. I put my example value in quotes just to show that it was a value pulled from a field; sorry about the accidental implication that it might be a string.


I like your way of coercing the type to text by adding & “”. That did the trick nicely. THANKS!

The ideal way to do this would be to use the DATE_FORMAT function like this:


DATETIME_FORMAT(InvoicedDate,'YY')


You can find a list of supported format specifiers here:




Heh. I think I’ve asked this question or one very like it before and gotten this good answer. This to me isn’t quite as intuitive as coercing the type to string by adding & “” as @augmented suggested. But your suggestion is one I need to remember, too. Thanks!

Reply


Terms & Conditions