Search code examples
sql-serverdateadd

sql server update with dateadd changing format

update calendar
set duedate = DATEADD(mm,1,duedate)
from calendar as t1
inner join events as t2
on t1.person = t2.[person]
where t2.catastrophy ='FamilyDeath'

I want to postpone a duedate by one month if there is a family death.

My dates are in the format of 04/30/2015. I run the above query, and it will take the date and change it to May 30 201. If I try to run it again I get an error stating:

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

Solution

  • I think because the type is char(10), you're getting a conversion you don't want. You're converting from CHAR to datetime, then just storing that as a CHAR. When you do the dateadd, try putting it back into the right CHAR format like this:

    set duedate = convert(char(10),dateadd(mm,1,dueDate),101)

    CONVERT allows you to specify a format, and 101 is "mm/dd/yyyy"

    https://msdn.microsoft.com/en-us/library/ms187928.aspx