Convert string to datetime to year and month SQL

Question

How do i get the year(2009) and month(12) from the string datetime, following give me correct full date but wrong year (1905-07-03 00:00:00.000) and month (1900-01-13 00:00:00.000). I have tried changing YYYY to year and MM to month.

Declare @date dateTime; Declare @CurrentYear datetime; Declare @CurrentMonth datetime; Select @date = CONVERT ( datetime , '20091231' ,112 ); Select @CurrentYear = DATEPART(YYYY,@date); --Select @CurrentYear = YEAR(@Date); <---- still wrong year Select @CurrentMONTH = DATEPART(MM,@date); --Select @CurrentMonth = MONTH(@date); <---- still wrong year select @date as fulldate, @CurrentYear as [year], @CurrentMonth as [Month];

None of the SO suggestions has worked so far.

regards K

marc_s · Accepted Answer · 2010-02-11 18:04:50Z

If you want to use DATEPART, use YEAR (YY or YYYY) or MONTH (M or MM) for your year and month parts:

DECLARE @date DATETIME SET @date = CAST('20091231' as DATETIME) -- ISO-8601 format always works SELECT DATEPART(YEAR, @date), -- gives 2009 DATEPART(YYYY, @date), -- gives 2009 DATEPART(MONTH, @date), -- gives 12 DATEPART(MM, @date) -- gives 12

Does that help at all??

SQLMenace · Accepted Answer · 2010-02-11 18:00:32Z

1

does this work?

 declare @d datetime select @d = '20091231' select YEAR(@d),MONTH(@d), year(getdate()) as CurrentYear

answered Feb 11, 2010 at 18:00

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

1 Comment

TheOCD Over a year ago

sort of.. assignment to @CurrentYear and @CurrentMonth was causing the dates to go wrong, when i did "select DATEPART(YYYY,@date) as [year],DATEPART(MM,@date) as [Month]" it worked

Collectives™ on Stack Overflow

Convert string to datetime to year and month SQL

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related