CODEWITHSUNDEEP
sqlsql-server-2008datetime
Saranya Rajendran
Saranya Rajendran

Reputation: 757

Splitting a datetime and replace the year part

I need to split a date like '01/12/15' and replace the year part from 15 to 2015 (ie; 01-12-2015). I get the year by the sql query:

select YEAR('10/12/15')

It returns the year 2015. but I have to replace 15 to 2015. how do i achieve this. Anyone here please help me. thanks in advance..

Edited:

I've tried following query too..

declare @date  varchar='10/12/2015' 
declare @datenew date

SELECT @datenew=CONVERT(nvarchar(10), CAST(@date AS DATETIME), 103)    

print @datenew

but it throws some error like this : Conversion failed when converting date and/or time from character string.

How do I change the varchar to date and replace its year part to 4 digit.. please help me..

DECLARE @intFlag INT,@date varchar(150),@payperiod numeric(18,0),@emp_Id  varchar(50)
SET @intFlag = 1  
declare @count as int set @count=(select count(*) from @myTable)  
WHILE   (@intFlag <=@count)  
BEGIN    
select @emp_Id=Employee_Id from @myTable where rownum=@intFlag     
select @date=attendance_date from @myTable where rownum=@intFlag    
declare @datenew datetime 
SELECT @datenew=convert(datetime,CONVERT(nvarchar(10), CAST(@date AS DATETIME), 103) ,103)

It throws the error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

Upvotes: 2

Views: 527

Answers (6)

Mike Clark
Mike Clark

Reputation: 1870

Try using:

Declare @date  nvarchar(20)='10/12/15' 
Declare @datenew nvarchar(40)
SELECT @datenew=CONVERT(nvarchar(10), CAST(@date AS DATETIME), 103)    
Print @datenew

Upvotes: 1

JassyJov
JassyJov

Reputation: 204

Problem with your query is that you haven't specified length for varchar datatype:

declare @date  varchar(12)='10/12/2015' 
declare @datenew date

SELECT @datenew=CONVERT(nvarchar(10), CAST(@date AS DATETIME), 103)    

print @datenew

Upvotes: 1

JassyJov
JassyJov

Reputation: 204

What datatype is the value '01/12/15'?

Maybe this will help you:

SELECT YEAR(CAST('10/12/15' AS DATE))

Upvotes: 1

Tjasun
Tjasun

Reputation: 176

If you want to change the format and get the varchar try to use:

SELECT CONVERT(varchar(10), CAST('10/12/15' AS DATETIME), 105)

I hope it helps.

Upvotes: 1

Ullas
Ullas

Reputation: 11566

As you said your column is in varchar type, try the following

Query

CREATE TABLE #temp
(
   dt VARCHAR(50)
);

INSERT INTO #temp VALUES
('01/12/15'),
('02/12/15'),
('03/12/15'),
('04/12/15'),
('05/12/15');

UPDATE #temp
SET dt = REPLACE(LEFT(dt, LEN(dt) - 2) 
       + CAST(YEAR(CAST(dt AS DATE)) AS VARCHAR(4)), '/', '-');

SELECT * FROM #temp;

EDIT

While declaring the variable @date you have not specified the length.
Check the below sql query.

declare @date  varchar(10)='10/12/2015' 
declare @datenew date
SELECT @datenew=CONVERT(nvarchar(10), CAST(@date AS DATETIME), 103)   
print @datenew

Upvotes: 1

Bhavesh Harsora
Bhavesh Harsora

Reputation: 693

The simplest way by casting Date object to VARCHAR

SELECT RIGHT(CAST(YEAR('10/12/15') AS VARCHAR), 2)

Upvotes: 1

Related Questions