I would like to select the current year from the system and append '02/01' to it. I coded: SELECT CAST('02/01/' + YEAR(GETDATE()) as date) but it throws the error message:
Explicit conversion from data type int to date is not allowed.
Any help would be appreciated.
That's because the + operator is an overloaded operator, meaning that it does different things depending on the usage.
You are trying to use it to do string concatenation, but because YEAR(GETDATE()) returns an int datatype, which has a higher datatype precedence than varchar, then SQL assumes you are using + to do integer addition. And so tries to add the string '02/01' to the integer 2021 - hence the error.
What you need to do is return the year as a varchar, and then SQL will happily concatenate the strings
e.g.
SELECT CAST('02/01/' + cast(year(GETDATE())as varchar) as date)
or
SELECT CAST('02/01/' + datename(year,getdate()) as date)
SELECT CAST('02/01/' + CAST(YEAR(GETDATE()) AS CHAR(4)) AS DATE)
My question would be... why do you need the string version of a date and then why do you need it in the seriously ambiguous format of mm/dd/yyyy?
If you're using at least SQL Server 2012 (and, by now, you should be well past even that now nearly a decade old and unsupported version), then consider creating an actual date/time datatype and formatting it only for when you need to display it in a controlled format.
To create the date, it's wicked easy (and has better performance) with DATEFROMPARTS...
SELECT DATEFROMPARTS(YEAR(GETDATE()),2,1);
If you absolutely insist on formatting the date, it's also easy after that...
SELECT CONVERT(CHAR(10),DATEFROMPARTS(YEAR(GETDATE()),2,1),101);
Whatever you do, don't permanently store formatted dates. Whatever the problem you think you're solving is, you've just create several other problems.
16 People are following this question.
