Clever Geek Handbook

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?

+3


source to share


3 answers


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)
+5


source


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.

0


source


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

0


source






More articles:


All Articles