CODEWITHSUNDEEP
sqlstringdatetime
TheOCD
TheOCD

Reputation: 161

Convert string to datetime to year and month SQL

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

Upvotes: 1

Views: 15835

Answers (2)

marc_s
marc_s

Reputation: 754388

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??

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 134961

does this work?

 declare @d datetime
 select @d = '20091231'

 select YEAR(@d),MONTH(@d),  year(getdate()) as CurrentYear

Upvotes: 1

Related Questions