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?
 
sdstuberConnect With a Mentor Commented:
right(yourcolumn,4)
0
 
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
 
deightonConnect With a Mentor progCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sdstuberConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.