Solved

how to get year part from nvarchar  date column

Posted on 2012-04-11
11
429 Views
Last Modified: 2012-04-26
how to get year part from nvarchar column having dates in the format 29/10/2008. I just need '2008' from this date
0
Comment
Question by:mmalik15
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 37832802
right(yourcolumn,4)
0
 

Author Comment

by:mmalik15
ID: 37832887
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
 
LVL 18

Assisted Solution

by:deighton
deighton earned 100 total points
ID: 37832958
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 37833046
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37833049
oops too slow, looks like somebody else came up with something similar
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:mmalik15
ID: 37833240
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37833287
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37833291
I demonstrated with my case statement but you could use the other case as well
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 37836683
Can you try this,

SELECT YEAR( CONVERT(DATETIME ,'29/10/2008' ,103) ) AS V_Year
0
 
LVL 18

Expert Comment

by:deighton
ID: 37836731
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
 

Author Closing Comment

by:mmalik15
ID: 37896709
sorry for delay but excellent comments.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now