February 19, 2016 at 2:53 pm
I am updating Column2 with the year only from Column1. Since they both have DATE as data types, when I use the YEAR() or DATEPART() function they return an INT data type and therefore I get the error:
Operand type clash: int is incompatible with date
So I've tried converting it and I still get the same error
UPDATE [dbo].
SET [COLUMN2] = CONVERT(INT,DATEPART(YEAR,[COLUMN1]))
If anyone could point me in the right direction on how to fix this.
Thanks.
February 19, 2016 at 5:50 pm
giszzmo (2/19/2016)
I am updating Column2 with the year only from Column1. Since they both have DATE as data types
As you said yourself, Column2 is a DATE. Therefore, whatever you put assign to it has to be a valid date.
Try the code below to give you an idea of what you need to do:
Column2 = cast(cast(year(Column1) as char(4)) + '-01-01' as date)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 19, 2016 at 8:33 pm
If you want to update Column2 to be the year of Column1, then Column2 needs to be an integer. The DATEPART function is the right one to use, but the data type needs to be corrected.
February 22, 2016 at 9:08 am
This doesn't work. I only need the year part and it's not a char.
February 22, 2016 at 9:22 am
giszzmo (2/22/2016)
This doesn't work. I only need the year part and it's not a char.
Can you explain "doesn't work"? What doesn't work? The code posted by LinksUp looks like it should work just fine based on your description.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 22, 2016 at 9:31 am
If Column2 is a Date column, you need to assign a complete date. The year is not a date, it's an integer value. If you want to change the data type of Column2, you need to use the ALTER TABLE statement, not the UPDATE with CONVERT.
The common practice is to set January 1st when indicating just the year. That's the reason for this to work:
UPDATE [dbo].
SET
Column2 = cast(year(Column1) as char(4));
February 22, 2016 at 9:36 am
giszzmo (2/22/2016)
This doesn't work. I only need the year part and it's not a char.
Then you need to insert it into an INT column.
A year on it's own is not a valid date, therefore you cannot put it in a DATE column. Imagine if you asked me what the date today was and I said '2016'. It's not a date, it's an integer.
Check out this link for valid date/time formats: https://msdn.microsoft.com/en-GB/library/ms187928(v=sql.110).aspx
February 22, 2016 at 11:24 am
Ed Wagner (2/19/2016)
If you want to update Column2 to be the year of Column1, then Column2 needs to be an integer. The DATEPART function is the right one to use, but the data type needs to be corrected.
February 22, 2016 at 1:15 pm
Of course you could also use a computed column where the definition is simply YEAR(column1).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply