how to get year part from nvarchar date column

how to get year part from nvarchar column having dates in the format 29/10/2008. I just need '2008' from this date
mmalik15Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
right(yourcolumn,4)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mmalik15Author Commented:
excellent sdstuber.

Can I also ask you (though its not part of orignal question) how to check if the nvarchar column has the format 'dd/mm/yyyy' before using the right function as i have just seen some of the values are not in the right format they are like 'American Journal of Gastroenterology 2005;100(4):936-971' etc for these values I want to keep a blank value for the year
0
deightonprogCommented:
DECLARE @YOURCOLUMN nvarchar(20);

set @YOURCOLUMN = '29/02/2006';

SELECT CASE WHEN
            ISNUMERIC(LEFT(@YOURCOLUMN,2)) = 1 AND
            ISNUMERIC(SUBSTRING(@YOURCOLUMN, 4,2)) = 1 AND
            ISNUMERIC(RIGHT(@YOURCOLUMN,4)) = 1 AND
            ISDATE(RIGHT(@YOURCOLUMN,4)  + SUBSTRING(@YOURCOLUMN, 4,2) + LEFT(@YOURCOLUMN,2)) = 1
            
            THEN
                  RIGHT(@YOURCOLUMN,4)
            ELSE
                  ''      
            END
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
probably should have been a new question, but you could try something like this...


       CASE
           WHEN     len(yourcolumn) = 10
                AND patindex('__/__/____', yourcolumn) = 1
                AND isnumeric(substring(yourcolumn, 1, 2)) = 1
                AND isnumeric(substring(yourcolumn, 4, 2)) = 1
                AND isnumeric(substring(yourcolumn, 7, 4)) = 1
           THEN
               substring(yourcolumn, 7, 4)
       END
0
sdstuberCommented:
oops too slow, looks like somebody else came up with something similar
0
mmalik15Author Commented:
i m tyring to create a small function for this but getting incorrect syntax error

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- Author:		Attila Kocsis
-- =============================================
Create FUNCTION dbo.GetYearSafe
(
	@datestring as varchar(4000)
)
RETURNS VARCHAR(4)
AS
BEGIN

	IF (ISDATE(@datestring)=0)
	BEGIN
		RETURN '';
	END
   
SELECT CASE WHEN 
            ISNUMERIC(LEFT(@datestring,2)) = 1 AND
            ISNUMERIC(SUBSTRING(@datestring, 4,2)) = 1 AND
            ISNUMERIC(RIGHT(@datestring,4)) = 1 AND
            ISDATE(RIGHT(@datestring,4)  + SUBSTRING(@datestring, 4,2) + LEFT(@datestring,2)) = 1
            
            THEN 
                  return RIGHT(@datestring,4);
END
                 ELSE 
                 RETURN '';
            END

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

0
sdstuberCommented:
don't put the return inside the case and you don't want a select, just return the case itself


Create FUNCTION dbo.GetYearSafe
(
      @datestring as varchar(4000)
)
RETURNS VARCHAR(4)
AS
BEGIN

      IF (ISDATE(@datestring)=0)
      BEGIN
            RETURN '';
      END
   
return CASE
           WHEN     len(@datestring) = 10
                AND patindex('__/__/____', @datestring) = 1
                AND isnumeric(substring(@datestring, 1, 2)) = 1
                AND isnumeric(substring(@datestring, 4, 2)) = 1
                AND isnumeric(substring(@datestring, 7, 4)) = 1
           THEN
               right(@datestring,4)
       END            
END
0
sdstuberCommented:
I demonstrated with my case statement but you could use the other case as well
0
sarabhaiCommented:
Can you try this,

SELECT YEAR( CONVERT(DATETIME ,'29/10/2008' ,103) ) AS V_Year
0
deightonprogCommented:
Create FUNCTION dbo.GetYearSafe
(
      @datestring as varchar(4000)
)
RETURNS VARCHAR(4)
AS
BEGIN

      IF (ISDATE(@datestring)=0)
      BEGIN
            RETURN '';
      END

DECLARE @RETVAL varchar(4000);
   --set the return value in the select statement
SELECT @RETVAL = CASE WHEN
            ISNUMERIC(LEFT(@datestring,2)) = 1 AND
            ISNUMERIC(SUBSTRING(@datestring, 4,2)) = 1 AND
            ISNUMERIC(RIGHT(@datestring,4)) = 1 AND
            ISDATE(RIGHT(@datestring,4)  + SUBSTRING(@datestring, 4,2) + LEFT(@datestring,2)) = 1
           
            THEN
                   RIGHT(@datestring,4)

                 ELSE
                  ''
            END

RETURN @RETVAL;

END

GO
0
mmalik15Author Commented:
sorry for delay but excellent comments.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.