Building dates in TSQL

Question

Trying to build a date:

CAST('9/1/' + YEAR(GETDATE()) AS Datetime) AS test2

But does it not work?

Would you like to get something like "9/1/2010"?

Answer 1

you cannot concatenate string '9/1'with number: YEAR(GETDATE())so try the following:

select CAST ('9/1 /' + CONVERT (varchar (4), YEAR (GETDATE ())) AS Datetime) AS test2

Answer 2

SELECT 
 CAST( '9/1/' + CAST( YEAR(GETDATE()) AS VARCHAR ) AS Datetime) AS test2 

You need to pass YEAR (integer) to VARCHAR before you can add it.

Answer 3

try the following:

Select DateAdd(month, 
           dateDiff(month, 0, getdate()) + 9 - MONTH(getdate()),
           0)

Answer 4

You can use the string format YYYYMMDD.