CODEWITHSUNDEEP
sqlsql-serverdatetime
user4008039
user4008039

Reputation:

Get current year first month and first day in SQL

I have a SP and I want to get the current year and the first day and first month of the year.

For example, for this year : 2014-01-01; For 2015 : 2015-01-01;

I tried 

@data datetime , @test datetime

SELECT @test = GETDATE()
set @test = CAST( YEAR(@test)+'-'+'01'+'-'+'01' as char(100))
set @data = CAST( @test as datetime)

But it returns 2016 not 2014 any help?

Upvotes: 3

Views: 6198

Answers (3)

nvm-uli
nvm-uli

Reputation: 656

you can just modify your snipped a little bit:

declare @data datetime , @test datetime

SELECT @test = GETDATE()
set @test = convert(nvarchar(4),YEAR(@test)) +'-'+'01'+'-'+'01'
set @data = @test

this will work for you

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

Your problem is that your are adding day and month to a numeric value. year() returns an integer.

Implicit casting '-' as an int returns 0.

So your expression would result in 2014+0+1+0+1 because when you have one integer and attempting to add (var)chars, sqlserver will attempt to convert everything else to integers.

The best way to manipulate the date to the first day of the year probably is this:

SELECT DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)

Upvotes: 5

Joël Salamin
Joël Salamin

Reputation: 3576

Here is a solution to get the result you want:

DECLARE @test       DATETIME

SET @test = CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(4)) + '-01-01'

SELECT @test

The conversion to DATETIME is made automatically when you set the value.

Hope this will help you.

Upvotes: 0

Related Questions