Get current year first month and first day in SQL
I have an SP and I want to get the current year and first day and first month of the year.
For example, this year: 2014-01-01;
For2015 : 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 and not 2014 any help?
Your problem is that you are adding the day and month to the numeric value. year () returns an integer.
Implicit casting '-' when int returns 0.
So your expression will result in 2014 + 0 + 1 + 0 + 1, because when you have one integer and try to add (var) characters, sqlserver will try to convert everything else to integers.
The best way to manipulate the date on the first day of the year is probably this:
SELECT DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)
Here is a solution to get the desired result:
DECLARE @test DATETIME
SET @test = CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(4)) + '-01-01'
SELECT @test
The conversion to DATETIME
is done automatically when you set a value.
Hope this helps you.
you can just change your text a little:
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