CODEWITHSUNDEEP
sqlsql-serverdate
user11333092
user11333092

Reputation:

How to insert date this year i.e. 30/7/CurrentYear

I want to write a script that will insert the month and date consistently but use the current year. I have tried this:

DECLARE @startDateVar DATETIME = '31/7/'+year(getdate())

But it doesn't work with error

Conversion failed when converting the varchar value '31/7/' to data type int.

Ideally I can capture the current year and pass it to the constructor for the DATETIME variable. What is the best way to do this?

Upvotes: 1

Views: 585

Answers (3)

Qirel
Qirel

Reputation: 26450

Use the proper date-format YYYY-MM-DD to avoid ambiguity, and use CONCAT() to join the strings together (as YEAR() returns an int, you need to either use CONCAT() to concatenate it, or cast it to a string before you can add it to your other string).

DECLARE @startDateVar DATETIME = CONCAT(YEAR(GETDATE()), '-07-31')

Upvotes: 1

Thom A
Thom A

Reputation: 95561

You could use DATEFROMPARTS:

SELECT DATEFROMPARTS(YEAR(GETDATE()),7,31);

Upvotes: 1

Arulkumar
Arulkumar

Reputation: 13237

SELECT GETDATE() will return as 2019-05-16 08:35:03.257, so if you are assign the value in yyyy-mm-dd format it will accept.

So using CONCAT() with the yyyy-mm-dd format helps:

DECLARE @startDateVar DATETIME = CONCAT(year(getdate()), '-07-31')
SELECT @startDateVar

the output will be 2019-07-31 00:00:00.000

Upvotes: 0

Related Questions