Adding 60days to a date problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding 60days to a date problem

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-12-02 : 11:01:46

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-02 : 11:05:57

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-02 : 11:08:12

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-12-02 : 11:36:32

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"

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-12-02 : 11:43:53

its something like CONVERT(VARCHAR(10), Date_Approved_By_Risk__c, 106)
but when i add the +60 i get an error message :(

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-02 : 11:54:12

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-12-02 : 11:57:27

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-12-02 : 11:59:00

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-02 : 12:10:09

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-02 : 12:10:45

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources