Click here to Skip to main content
15,302,644 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have..
Table_A
Table_B

SQL
INSERT INTO Table_B (Price, Modified)
SELECT SUM(Price) AS Sales, YEAR(Modified) YEAR
FROM Table_A
GROUP BY YEAR(Modified) ORDER BY YEAR

I need as this result

Name - Price - ModifiedDate
--------------------------------
nn - 1000 - 2015
cc - 5000 - 2016 total of days and months for 2016 year
but insert this total into another table as is.
if I have to use Fetch courser also dose not matter

but I need this result
nn - 1000 - 2010
cc - 5000 - 2013
nn - 1000 - 2015
cc - 5000 - 2016
YEAR only without month and days
thank you..

What I have tried:

SQL
SELECT SUM(Price) AS Sales, YEAR(ModifiedDate) YEAR
FROM Table_a
GROUP BY YEAR(ModifiedDate) ORDER BY YEAR


BUT this SUM at run time only with out inserting into Table_B

I tryed also

SQL
SELECT SUM(Price ) AS Sales, DATEPART(YYYY, ModifiedDate) YEAR
FROM SalesDate
GROUP BY YEAR(ModifiedDate) ORDER BY YEAR(ModifiedDate)

SQL Manager
showing this error...
Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with date
Posted
Updated 12-Dec-21 22:53pm
v5
Comments
[no name] 27-May-17 7:12am
   
Looks like you Need to cast the year to DATETIME
Maciej Los 28-May-17 5:00am
   
Unclear... Please, provide example data (table_a).
an0ther1 28-May-17 17:55pm
   
As 0x01AA has advised - I would guess you need to revisit your table structure.
DATEPART(yyyy, <datevalue>) returns an Integer, not a Date
YEAR(<datevalue>) also returns an Integer, not a Date
If Table_B.Modified has a data-type of Date you would need to convert your Integer to a Date or change the data-type of the Modified column to an Int

Kind Regards

1 solution

I am not sure about what do you intent. I think that the second column of the insert "must be" a date, and the YEAR(...) returns a integer, so a error succeds.

Please try to use:
INSERT INTO table_b(Price, Modified) <br />
SELECT SUM(price) as sales, convert(datetime,CONVERT(char(4),YEAR(Modified))+'-01-01T00:00:00') as Modified from table_a<br />
group by convert(datetime,CONVERT(char(4),YEAR(Modified))+'-01-01T00:00:00')<br />
order by 2
.

Explanation:
Year(Modified) return a int with the year of modified, but CONVERT(char(4),YEAR(fecha)) converts this year a string (for example "2017").
Then CONVERT(char(4),YEAR(Modified))+'-01-01T00:00:00', is a string that represents the first day of that year at the 0:00 hours (that is "2017-01-01T00:00:00".
For last convert(datetime,CONVERT(char(4),YEAR(Modified))+'-01-01T00:00:00') converts that string to a datetime corresponding to the first day of the year.
(Note that kind of string aaaa-mm-ddThh:mm:ss doesn´t depends of the culture or idiom so always is interpreted correctly).
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900