adding years to a date time from a varchar value

I am trying to create a date like so

expirdate=dateadd(YEAR, 8, d.taxyear)

This should make this expire date be 8 years from the tax year.

This tax year column is a varchar....
I am still getting error when I try and convert the tax year to a date or whatever it needs to be to make this work.

In the end the expire date should be 8 more years from the year value in the taxyear.

This is the error

Conversion failed when converting date and/or time from character string.
LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?
 
Ares KurkluSoftware EngineerCommented:
ok double conversion is required i guess

 select CONVERT(VARCHAR,( convert(varchar, cast(d.taxyear as int) + 8)) +'-'+'12'+'-'+'31', 101)
1
 
yo_beeDirector of Information TechnologyCommented:
D.taxyear looks like '2018/04/15'
0
 
Ares KurkluSoftware EngineerCommented:
So the convert like below is still giving an error message? can you give an example of the sting looks?

 DATEADD(YEAR,8,CONVERT(VARCHAR, d.taxyear, 101))

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Robb HillSenior .Net DeveloperAuthor Commented:
no tax year looks like 2018 ...etc
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
but the end result should be a datetime for expiredate
0
 
yo_beeDirector of Information TechnologyCommented:
That's your issue.

Dateadd needs a complete date like the one I posted.
If you are looking to display only the year you need to do this

select year(dateadd(YEAR, 8, d.taxyear)) as ExpireDate

Open in new window

0
 
Ares KurkluSoftware EngineerCommented:
Can you please give an input example and what your expected output is ? is the string full date and do you just want to get the year back?
0
 
yo_beeDirector of Information TechnologyCommented:
Sorry about my post.  It is not correct with the data you are trying to add.
d.taxyear needs to be a full date of you can try this

what I think it will look like with your data
Select (D.taxyear + 8) AS ExpireDate

Open in new window

0
 
Robb HillSenior .Net DeveloperAuthor Commented:
ok so lets say tax year is a varchar and the value is 2018
and I am inputing 8 to increment tax year by  8.

Then that should increment taxyear to 2018 + 8

But expire date would be in this format   2018-12-31 00:00:00.000  + 8 years
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
ok so that got taxyear correct...now I just need that as a datetime
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
Then ...my last part of this..is how would I update this using the following select statement.  

I need to update the column expiredate to this new value...from this select statement.....ndocument in the query would be unique.
0
 
yo_beeDirector of Information TechnologyCommented:
So you want to take a year 2018 and have an output of 2026-12-31 00:00:00.000
1
 
Robb HillSenior .Net DeveloperAuthor Commented:
SELECT d.ndocument, d.udf1, d.created,d.taxyear,t1.Name,t1.Parent,t1.Policy, t1.value,d.expirdate
,(D.taxyear + 8) AS ExpireDate
FROM ..Documents d
inner join
(
SELECT CASE WHEN CHARINDEX('!', l.value) > 0 THEN
    LEFT(value, CHARINDEX('!', value)-1) ELSE
    value END as value, name, Parent,
    CASE WHEN value LIKE '%!%' THEN RIGHT(value,CHARINDEX('!',REVERSE(value))-1) 
           END as Policy
  FROM [Cadoc_Ext].[dbo].[lookupexport] l

)t1
on d.udf1 = t1.value
where Policy = 8 and year(d.expirdate) != '9999'

Open in new window

0
 
Robb HillSenior .Net DeveloperAuthor Commented:
yes
0
 
yo_beeDirector of Information TechnologyCommented:
I think you need to post a small data set and mock up an output.
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
that gets the correct tax year...now it just needs to be converted to a datetime ....so

D.taxyear + 8
needs to have mm/dd and time added to it to make it datetime
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
This is what im getting with this method

cast((D.taxyear + 8) + '-' + '12' + '31' as datetime)

tax year is 2008
converts to 2016

and when I cast it alltogether it gives me 1905-08-22 00:00:00.000

which is obviously not want I want.

It should be 2016-12-31 00:00:00.000
0
 
Ares KurkluSoftware EngineerCommented:
you don't have the '-' between  '12' + '31'
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
cast((D.taxyear + 8) + '-' + '12' + '-' + '31' as datetime)


THis still gives the strange 1905 date
0
 
Ares KurkluSoftware EngineerCommented:
i think you should use convert there because something like this should work:

            select CONVERT(VARCHAR,'2018'+'-'+'12'+'-'+'31', 101)
0
 
Ares KurkluSoftware EngineerCommented:
in your case if d.taxyear is just 2018 etc.. :

 CONVERT(VARCHAR,(D.taxyear + 8)+'-'+'12'+'-'+'31', 101)
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
your method alone workds..

but if I inject this in place of your 2018 it messes it up

(D.taxyear + 8)  This will not sub in year parameter I think
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
that yields 2059
0
 
Ares KurkluSoftware EngineerCommented:
is D.taxyear just the year like 2018 of full date? if it is a full date too then you need to do datepart

DATEPART(year, D.taxyear ) +8
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
d.taxyear is just a year.....but its a varchar value..so yes in the example it would be 2018 as varchar
0
 
Ares KurkluSoftware EngineerCommented:
then first you need to cast that as integer

 CONVERT(VARCHAR,( cast(D.taxyear as int) + 8)+'-'+'12'+'-'+'31', 101)
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
its still 2059
0
 
yo_beeDirector of Information TechnologyCommented:
I just set my value as a varchar and I get the same results 2026

So d.taxyear + 8 = 2059
0
 
Ares KurkluSoftware EngineerCommented:
Ok yea we didn't even need to conver to int in the first place as it is a char anyway,

What is the pure output when you do
select d.taxyear ..  ?
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
it works except when trying to piece it into yy/mm/dd time
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
select d.taxyear will return a 4 digit varchar that looks like a year value
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
Double conversion it was....omg what a pain...lol
0
 
Ares KurkluSoftware EngineerCommented:
You needed to cast from varchar to int then to do the math then convert to varchar back to form the date
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
I was trying to do a concatanate as well but no luck ..but this works...I only need this to update some millions of records...so just need to make sure the math is correct on the date.

Thank you all so much!  I will post in another forum on my update and go ahead and award the points for all the help here.
0
 
yo_beeDirector of Information TechnologyCommented:
select  dateadd(month,12,(Dateadd(year,8'2018')))-1  

Open in new window

this will give the results  I think

 select convert(varchar(11),(dateadd(month,12,(Dateadd(year,8,'2018')))-1),11)

Open in new window

0
 
Robb HillSenior .Net DeveloperAuthor Commented:
Thank you both!
0
 
yo_beeDirector of Information TechnologyCommented:
Robb Please try my last one.  I think this will work as well.
0
 
Ares KurkluSoftware EngineerCommented:
You're welcome.
0
 
Mark WillsTopic AdvisorCommented:
Just as a FYI, in SQL2016 we can now do
SELECT DATEFROMPARTS ( cast(d.taxyear as int) + 8, 12, 31 ) AS ExpireDate 

Open in new window

So, always worthwhile including the version of SQL Server you are using :)
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.