CODEWITHSUNDEEP
sqlsql-serverdatetime
Isaac
Isaac

Reputation: 351

Convert date to SQL datetime

I'm somewhat new to T-SQL and despite reading a number of articles that indicate this should work, I'm having trouble converting October 1st of the current year to a datetime.

I've tried:

SELECT CAST(DATEPART(year, GETDATE()) + '1015' AS DATETIME)

SELECT CONVERT(datetime, 'Oct 15 ' + DATEPART(YEAR,GETDATE()),100)

And all kinds of variations.

Any ideas? I need to set a datetime variable to whatever Oct 1st of the current year is.

Upvotes: 0

Views: 269

Answers (2)

Ryan B.
Ryan B.

Reputation: 3665

What you're trying to is close, but DATEPART returns a number, so the "+" is doing addition, not concatenation.

Try it like this:

SELECT CAST(CAST(DATEPART(year, GETDATE()) AS VARCHAR(4)) + '1015' AS DATETIME)

edit -- Ed beat me to it, and the Concat function is better too.

But if you really wanted to knock it out of the park, try this...

SELECT DATEADD(month, 9, DATEADD(year, DATEDIFF(year, 0, getdate()), 0)) As October1CurrentYear

No casting required!

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26363

Your first query is very close. The problem is that the plus sign (+) for concatenation is actually giving you a numeric value, which you can't cast to a date.

To concatenate a year and '1015' and end up with a string, use the CONCAT function instead:

SELECT CAST(CONCAT(DATEPART(YEAR, GETDATE()), '1015') AS DATE)

Upvotes: 0

Related Questions