Constructing dates in TSQL

Question

Trying to construct a date:

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

But it doesnt work?

Would like to get something like '9/1/2010'?

Answer 1

you can't concatenate the string '9/1' with the number: YEAR(GETDATE()), so try this:

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

Answer 2

You can use a string formatted YYYYMMDD.

Answer 3

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

You need to cast the YEAR (integer) to a VARCHAR before you can append it.

Answer 4

Where is the date coming from?

There are easier ways to build dates using DATEADD

Answer 5

try this:

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