question

sbutle7 avatar image
sbutle7 asked

How to append a fixed month and day to the current year in SQL

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.

sql2008r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

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)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Kev, that worked perfectly!!!

0 Likes 0 ·
anthony.green avatar image
anthony.green answered

SELECT CAST('02/01/' + CAST(YEAR(GETDATE()) AS CHAR(4)) AS DATE)

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.