现有A表和B表,
A表中有一列记录的是DATE类型的日期数据(类型是:2009-5-10 18:25:45), 我想把这列数据查询出来后取得年2009,月5,日期10
分别把2009,5,10插入到B表对应的三列CYEAR,CMONTH中, 改怎么做呢,我试过好多方法都不行,我把存储过程贴出来,请高手帮帮我,
红色的地方即为问题所在之处,谢谢
CREATE PROC [dbo].[import_consume_list_from_WxzyHis]
AS
BEGIN
DECLARE @YEAR INT
DECLARE @MONTH INT
IF (MONTH(GETDATE())=1)
BEGIN
SET @YEAR=(YEAR(GETDATE())-1)
SET @MONTH=12
END
ELSE
BEGIN
SET @YEAR=YEAR(GETDATE())
SET @MONTH=(MONTH(GETDATE()) -1)
END
BEGIN TRAN IMPORT
TRUNCATE TABLE import_consume_list
INSERT INTO import_consume_list(
Store_code,
Store_name,
Unit_id_his,
Unit_name_his,
Stuff_code,
Stuff_name,
Spec,
In_price,
Out_price,
Amount,
sum_price,
--serial,
cyear,
cmonth)
SELECT
STORAGECODE,
STORAGENAME,
DEPTCODE,
DEPTNAME,
THINGCODE,
THINGNAME,
SPECIFICATION,
PRICE_INT,
PRICE_OUT,
APPLYNUM,
OUTMONEY,
CYEAR,
CMONTH
FROM OPENQUERY(ora817,'SELECT A.STORAGECODE,B.STORAGENAME,A.DEPTCODE,C.DEPT_NAME,A.THINGCODE,
A.THINGNAME,A.SPECIFICATION,D.PRICE AS PRICE_INT,A.PRICE AS PRICE_OUT,
A.APPLYNUM,A.OUTMONEY,YEAR(A.OPERATEDATE) AS CYEAR,MONTH(A.OPERATEDATE) AS CMONTH
FROM WG_OUTPUT A,WG_STORAGE B,R_DEPARTMENT C,WG_INPUT D
WHERE A.STORAGECODE=B.STORAGECODE AND A.DEPTCODE=C.DEPT_CODE AND A.THINGCODE=D.THINGCODE
IF @@ERROR=0
COMMIT TRAN IMPORT
ELSE
ROLLBACK TRAN IMPORT
END
A表中有一列记录的是DATE类型的日期数据(类型是:2009-5-10 18:25:45), 我想把这列数据查询出来后取得年2009,月5,日期10
分别把2009,5,10插入到B表对应的三列CYEAR,CMONTH中, 改怎么做呢,我试过好多方法都不行,我把存储过程贴出来,请高手帮帮我,
红色的地方即为问题所在之处,谢谢
CREATE PROC [dbo].[import_consume_list_from_WxzyHis]
AS
BEGIN
DECLARE @YEAR INT
DECLARE @MONTH INT
IF (MONTH(GETDATE())=1)
BEGIN
SET @YEAR=(YEAR(GETDATE())-1)
SET @MONTH=12
END
ELSE
BEGIN
SET @YEAR=YEAR(GETDATE())
SET @MONTH=(MONTH(GETDATE()) -1)
END
BEGIN TRAN IMPORT
TRUNCATE TABLE import_consume_list
INSERT INTO import_consume_list(
Store_code,
Store_name,
Unit_id_his,
Unit_name_his,
Stuff_code,
Stuff_name,
Spec,
In_price,
Out_price,
Amount,
sum_price,
--serial,
cyear,
cmonth)
SELECT
STORAGECODE,
STORAGENAME,
DEPTCODE,
DEPTNAME,
THINGCODE,
THINGNAME,
SPECIFICATION,
PRICE_INT,
PRICE_OUT,
APPLYNUM,
OUTMONEY,
CYEAR,
CMONTH
FROM OPENQUERY(ora817,'SELECT A.STORAGECODE,B.STORAGENAME,A.DEPTCODE,C.DEPT_NAME,A.THINGCODE,
A.THINGNAME,A.SPECIFICATION,D.PRICE AS PRICE_INT,A.PRICE AS PRICE_OUT,
A.APPLYNUM,A.OUTMONEY,YEAR(A.OPERATEDATE) AS CYEAR,MONTH(A.OPERATEDATE) AS CMONTH
FROM WG_OUTPUT A,WG_STORAGE B,R_DEPARTMENT C,WG_INPUT D
WHERE A.STORAGECODE=B.STORAGECODE AND A.DEPTCODE=C.DEPT_CODE AND A.THINGCODE=D.THINGCODE
IF @@ERROR=0
COMMIT TRAN IMPORT
ELSE
ROLLBACK TRAN IMPORT
END
FROM OPENQUERY(ora817,'SELECT A.STORAGECODE,B.STORAGENAME,A.DEPTCODE,C.DEPT_NAME,A.THINGCODE,
A.THINGNAME,A.SPECIFICATION,D.PRICE AS PRICE_INT,A.PRICE AS PRICE_OUT,
A.APPLYNUM,A.OUTMONEY,YEAR(A.OPERATEDATE) AS CYEAR,MONTH(A.OPERATEDATE) AS CMONTH
FROM WG_OUTPUT A,WG_STORAGE B,R_DEPARTMENT C,WG_INPUT D
WHERE A.STORAGECODE=B.STORAGECODE AND A.DEPTCODE=C.DEPT_CODE AND A.THINGCODE=D.THINGCODE
AND YEAR(A.OPERATEDATE)= '+CAST(@YEAR AS VARCHAR(10))+'AND MONTH(A.OPERATEDATE)='+CAST(@month AS VARCHAR(10))')'
2、where子句基于指定的条件对记录行进行筛选;
3、group by子句将数据划分为多个分组;
4、使用聚集函数进行计算;
5、使用having子句筛选分组;
6、计算所有的表达式;
7、使用order by对结果集进行排序。
CREATE TABLE [time] (
[aa] [datetime] NULL
) ON [PRIMARY]
GO/*这个你要添加数据的表*/
CREATE TABLE [addtime] (
[Nyear] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Nmonth] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Nday] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
/*添加一行数据*/
insert into [time] values('2008-9-6')
/*向另一个表里添加数据*/
use shiyan
insert into addtime
select year(aa),month(aa),day(aa)
from [time]
/*复制上面斥可以看到结果了*/