SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding 60days to a date problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 12/02/2013 :  11:01:46  Show Profile  Reply with Quote
Hey All

I need some help and i am hoping you will be able to provide some assistance
Aim – To add 60days to [Date_Approved_By_Risk__c] column
Query so far is
select
[Date_Approved_By_Risk__c]
--left([Date_Approved_By_Risk__c],10)+60 as correct_date
from #build
where [Date_Approved_By_Risk__c] is not null

[Date_Approved_By_Risk__c] is in the following format “2010-04-30T23:00:00.000Z”

When i add the following “left([Date_Approved_By_Risk__c],10)+60 as correct_date” into the query i get the following error Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2010-07-13' to data type int.


Really looking forward to your help

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/02/2013 :  11:05:57  Show Profile  Reply with Quote
So you've got dates stored as varchar? Why not datetime?
anyway, try this:

select dateadd(day, 60, convert(datetime, Date_Approved_By_Risk__c))

or if it is already datetime then just remove the convert:

select dateadd(day, 60, Date_Approved_By_Risk__c)

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/02/2013 :  11:08:12  Show Profile  Reply with Quote

select convert(datetime,Date_Approved_By_Risk__c,127) + 60
FROM #build
where [Date_Approved_By_Risk__c] is not null


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 12/02/2013 :  11:36:32  Show Profile  Reply with Quote
Hi Visakh16

Thank you for your solution, however with your logic it produces the following results "2011-03-10 10:41:33.000" any way i can get the results to be produced like "2011-03-10"
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 12/02/2013 :  11:43:53  Show Profile  Reply with Quote
its something like CONVERT(VARCHAR(10), Date_Approved_By_Risk__c, 106)
but when i add the +60 i get an error message :(
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/02/2013 :  11:54:12  Show Profile  Reply with Quote
so you want the result as varchar rather than date?

select convert(varchar(10), dateadd(day, 60, convert(datetime, Date_Approved_By_Risk__c)), 121)

Be One with the Optimizer
TG
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 12/02/2013 :  11:57:27  Show Profile  Reply with Quote
HI TG

How can i find
Year,Month&WeekNum of select convert(varchar(10), dateadd(day, 60, convert(datetime, Date_Approved_By_Risk__c)), 121)

normally i would

datepart(yyyy,[Date_Approved_By_Risk__c]) as Year,
datepart(mm,[Date_Approved_By_Risk__c])+45 as Month,
DATEPART(WEEK, [Date_Approved_By_Risk__c])AS WeekNum

But that would look at the existing value not the new one
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 12/02/2013 :  11:59:00  Show Profile  Reply with Quote
Basically i need to change Date_Approved_By_Risk__c from 2010-04-30T23:00:00.000Z” into 2011-04-30" add 60days and then work out what year, month, and week num the new date format falls into
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/02/2013 :  12:10:09  Show Profile  Reply with Quote
what is the datetype of [Date_Approved_By_Risk__c]?


datepart(year, dateadd(day, 60, convert(datetime, Date_Approved_By_Risk__c)))
,datepart(month, dateadd(day, 60, convert(datetime, Date_Approved_By_Risk__c))
,datepart(week, dateadd(day, 60, convert(datetime, Date_Approved_By_Risk__c))
Be One with the Optimizer
TG

Edited by - TG on 12/02/2013 12:11:39
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/02/2013 :  12:10:45  Show Profile  Reply with Quote
Here is a step by step way to convert your date string:
DECLARE @BaseDate AS DATE;

SET @BaseDate = CONVERT(DATE, '2010-04-30T23:00:00.000Z', 127);

SELECT @BaseDate;

DECLARE @NewDate DATE;

SET @NewDate = DATEADD(DAY, 60, @BaseDate);

SELECT
	YEAR(@NewDate) AS [Year],
	MONTH(@NewDate) AS [Month],
	DATEPART(WEEK, @NewDate) AS [Week
You can then put that together to select from your table:
-- Sample Data
DECLARE @Foo TABLE (Date_Approved_By_Risk__c VARCHAR(50));

INSERT @Foo VALUES ('2010-04-30T23:00:00.000Z');

-- Commbine everythig to get results
WITH Cte AS
(
	SELECT   DATEADD(DAY, 60, CONVERT(DATE, Date_Approved_By_Risk__c, 127)) AS NewDate
	FROM @Foo
)

SELECT
	YEAR(NewDate) AS [Year],
	MONTH(NewDate) AS [Month],
	DATEPART(WEEK, NewDate) AS [Week]
FROM
	Cte
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000