CODEWITHSUNDEEP
sql
R0b0tn1k
R0b0tn1k

Reputation: 4306

Constructing dates in TSQL

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'?

Upvotes: 3

Views: 133

Answers (4)

Charles Bretana
Charles Bretana

Reputation: 146429

try this:

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

Upvotes: 1

Keith Adler
Keith Adler

Reputation: 21178

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.

Upvotes: 1

Kees de Kooter
Kees de Kooter

Reputation: 7195

You can use a string formatted YYYYMMDD.

Upvotes: 0

KM.
KM.

Reputation: 103579

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

Upvotes: 4

Related Questions