asked
Conversion failed when converting date and/or time from character string.
I am trying to update a date column using the below query which is giving me this error- Conversion failed when converting date and/or time from character string. update a set day_1=day(register_date) update a set date= convert(date,convert(varchar(4),yEAR(getdate())) + '-' + case when MONTH(getdate())>=10 then CONVERT(varchar(2),month(getdate())-1) else '0'+CONVERT(varchar(1),month(getdate())-1) end +'-'+ case when day_1=31 and month(getdate())-1 not in (1,3,5,7,8,10,12) then convert(varchar(2),day_1-1) when day_1=30 and month(getdate())-1 not in (4,6,9,11) then convert(varchar(2),day_1+0) when day_1>28 and month(getdate())=2 and year(getdate())%4=0 then convert(varchar(2),'29') when day_1>28 and month(getdate())=2 and year(getdate())%4<>0 then convert(varchar(2),'28') when day_1 >=10 then convert(varchar(2),day_1) else '0'+convert(varchar(1),day_1) end) It would be great if someone can help me to resolve this. Thanks
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
If you're trying to insert in to last_accessed_on, which is a DateTime2, then your issue is with the fact that you are converting it to a varchar in a format that SQL doesn't understand. If you modify your code to this, it should work, note the format of your date has been changed to: YYYY-MM-DD hh:mm:ss: replace with: CAST('2014-07-23 09:37:00.000' AS datetime2)
What happens when the current month is January? month(getdate())-1 = 0 But I assume you want 12 so you could instead use [DateAdd][1] with [DateDiff][2] DATEADD(month, DATEDIFF(month, 0, DATEADD(month,-1,GETDATE())), 0) This will give you the first day of the prior month - then all you have to do is use your CASE part instead of the 0 at the end to how many days you wish to add and you will have the date you want UPDATE a SET day_1 = DAY(register_date) UPDATE a SET day_1 = DAY(register_date) UPDATE a SET date = DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -1, GETDATE())), CASE WHEN day_1 = 31 AND MONTH(GETDATE()) - 1 NOT IN ( 1, 3, 5, 7, 8, 10, 12 ) THEN day_1 - 1 WHEN day_1 = 30 AND MONTH(GETDATE()) - 1 NOT IN ( 4, 6, 9, 11 ) THEN day_1 - 1 WHEN day_1 > 28 AND MONTH(GETDATE()) = 2 AND YEAR(GETDATE()) % 4 = 0 THEN '28' WHEN day_1 > 28 AND MONTH(GETDATE()) = 2 AND YEAR(GETDATE()) % 4 <> 0 THEN '27' WHEN day_1 >= 10 THEN day_1 - 1 ELSE day_1 - 1 END) [1]:
https://msdn.microsoft.com/en-us/library/ms186819.aspx [2]:
https://msdn.microsoft.com/en-GB/library/ms189794.aspx
20 People are following this question.
