关于SQL问题，急！！！！！！

Question

select convert(varchar(20),@OldDeliveryFactDate,120)

Answer 1

declare @OldDeliveryFactDate smalldatetime IF @OldDeliveryFactDate <>@DeliveryFactDate 
BEGIN 
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog 
@OrderBillCode=@OrderBillCode,  
@PartNumber=@OldPartNumber,  
@ModifyFieldName='实际发货日期',  
@ModifyLastValue=convert(varchar(20),@OldDeliveryFactDate,120), 
@ModifyFinishValue=@DeliveryFactDate, 
@UserName=@OldUserName,    
@ModifyDate=@ModifyDate 
END 

Answer 2

那是显示问题
select convert(char(19),datecol,120)
或修改你的DB关于datatime的格式

Answer 3

declare @OldDeliveryFactDate smalldatetime IF @OldDeliveryFactDate <>@DeliveryFactDate 
BEGIN 
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog 
@OrderBillCode=@OrderBillCode,  
@PartNumber=@OldPartNumber,  
@ModifyFieldName='实际发货日期',  
convert(varchar(20),@ModifyLastValue,120)=convert(varchar(20),@OldDeliveryFactDate,120), 
@ModifyFinishValue=@DeliveryFactDate, 
@UserName=@OldUserName,    
@ModifyDate=@ModifyDate 
END 

Answer 4

怎么楼上的都不对。报错完整的SQL是这样的set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*************************                        
功能名：网上商城：订单编辑，更新订单子表信息                     
创建人：吴永辉                       
创建日期：2007-8-1                        
**************************/  
ALTER       procedure [dbo].[CSMS_SHOP_UpdateOrderBillSub]  
@OrderBillSubID int,  
@BMCDeliveryDate smalldatetime,  
@DeliveryBillCode nvarchar(255), 
@DeliveryFactDate smalldatetime, 
@OrderQty int, 
@SupplyPrice money,
@TotalMoney money,  
@Re nvarchar(255),  
@IsDelete SmallInt,
@PartNumber nvarchar(255),
@CPartDesc Nvarchar(255),
@IsAdmin bit,
@OldUserName nvarchar(100)
as  declare @OrderBillCode  nvarchar(15)
declare @OldPartNumber  nvarchar(255)
declare @OldSupplyPrice money
declare @OldOrderQty    int
declare @OldBMCDeliveryDate smalldatetime
declare @OldDeliveryBillCode nvarchar(255)
declare @OldDeliveryFactDate smalldatetime
declare @OldRe       nvarchar(255)
declare @ModifyDate      SmallDateTime
SELECT @OrderBillCode=OrderBillCode
FROM CSMS_SHOP_PartOrderBillMain
WHERE OrderBillID=(SELECT OrderBillID FROM  CSMS_SHOP_PartOrderBillSub WHERE OrderBillSubID=@OrderBillSubID  )SELECT @OldPartNumber=PartNumber,@OldSupplyPrice=SupplyPrice,@OldOrderQty=OrderQty,
@OldBMCDeliveryDate=BMCDeliveryDate,@OldDeliveryBillCode=DeliveryBillCode,
@OldDeliveryFactDate=DeliveryFactDate,@OldRe=Re,@ModifyDate=getdate()
FROM CSMS_SHOP_PartOrderBillSub
WHERE OrderBillSubID=@OrderBillSubID  if  @IsAdmin=1   
 Begin  
  update CSMS_SHOP_PartOrderBillSub  
  set  
  BMCDeliveryDate=@BMCDeliveryDate,   
  DeliveryBillCode=@DeliveryBillCode,   
  DeliveryFactDate=@DeliveryFactDate,
  OrderQty=@OrderQty,    
  SupplyPrice=@SupplyPrice, 
  TotalMoney=@TotalMoney,
  Re=@Re,
  IsDelete=@IsDelete,
  PartNumber=@PartNumber,
  CPartDesc=@CPartDesc
  where  OrderBillSubID=@OrderBillSubID
 End  
else  
 Begin  
  update CSMS_SHOP_PartOrderBillSub  
  set  
  BMCDeliveryDate=@BMCDeliveryDate,  
  DeliveryBillCode=@DeliveryBillCode,   
  DeliveryFactDate=@DeliveryFactDate,
  OrderQty=@OrderQty,    
  SupplyPrice=@SupplyPrice, 
  TotalMoney=@TotalMoney,
  Re=@Re,
  IsDelete=@IsDelete,
  PartNumber=@PartNumber,
  CPartDesc=@CPartDesc  
  where  OrderBillSubID=@OrderBillSubID  
 EndIF @OldPartNumber<>@PartNumber
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='零件代码', 
@ModifyLastValue=@OldPartNumber,
@ModifyFinishValue=@PartNumber,
@UserName=@OldUserName, 
@ModifyDate=@ModifyDate
ENDIF @OldSupplyPrice<>@SupplyPrice
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='零件单价', 
@ModifyLastValue=@OldSupplyPrice,
@ModifyFinishValue=@SupplyPrice,
@UserName=@OldUserName,  
@ModifyDate=@ModifyDate
ENDIF @OldOrderQty<>@OrderQty
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='订购数量', 
@ModifyLastValue=@OldOrderQty,
@ModifyFinishValue=@OrderQty,
@UserName=@OldUserName,   
@ModifyDate=@ModifyDate
ENDIF @OldBMCDeliveryDate<>@BMCDeliveryDate
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='BMC预计发货日期', 
@ModifyLastValue=@OldBMCDeliveryDate,
@ModifyFinishValue=@BMCDeliveryDate,
@UserName=@OldUserName,  
@ModifyDate=@ModifyDate
ENDIF @OldDeliveryBillCode<>@DeliveryBillCode
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='发货单号', 
@ModifyLastValue=@OldDeliveryBillCode,
@ModifyFinishValue=@DeliveryBillCode,
@UserName=@OldUserName,  
@ModifyDate=@ModifyDate
ENDIF @OldDeliveryFactDate<>@DeliveryFactDate
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='实际发货日期', 
@ModifyLastValue=@OldDeliveryFactDate,
@ModifyFinishValue=@DeliveryFactDate,
@UserName=@OldUserName,   
@ModifyDate=@ModifyDate
ENDIF @OldRe<>@Re
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber='', 
@ModifyFieldName='备注', 
@ModifyLastValue=@OldRe,
@ModifyFinishValue=@Re,
@UserName=@OldUserName,   
@ModifyDate=@ModifyDate
END

Answer 5

select convert(varchar(19),@OldDeliveryFactDate,120)

Answer 6

select convert(varchar(19),@OldDeliveryFactDate,120) 写在哪里？我有点苯苯的 - -

Answer 7

ALTER       procedure [dbo].[CSMS_SHOP_UpdateOrderBillSub]   
@OrderBillSubID int,   
@BMCDeliveryDate smalldatetime,   
@DeliveryBillCode nvarchar(255),  
@DeliveryFactDate smalldatetime,  
@OrderQty int,  
@SupplyPrice money, 
@TotalMoney money,   
@Re nvarchar(255),   
@IsDelete SmallInt, 
@PartNumber nvarchar(255), 
@CPartDesc Nvarchar(255), 
@IsAdmin bit, 
@OldUserName nvarchar(100) 
as   declare @OrderBillCode  nvarchar(15) 
declare @OldPartNumber  nvarchar(255) 
declare @OldSupplyPrice money 
declare @OldOrderQty    int 
declare @OldBMCDeliveryDate smalldatetime 
declare @OldDeliveryBillCode nvarchar(255) 
declare @OldDeliveryFactDate smalldatetime 
declare @OldRe       nvarchar(255) 
declare @ModifyDate      SmallDateTime 
SELECT @OrderBillCode=OrderBillCode 
FROM CSMS_SHOP_PartOrderBillMain 
WHERE OrderBillID=(SELECT OrderBillID FROM  CSMS_SHOP_PartOrderBillSub WHERE OrderBillSubID=@OrderBillSubID  ) SELECT @OldPartNumber=PartNumber,@OldSupplyPrice=SupplyPrice,@OldOrderQty=OrderQty, 
@OldBMCDeliveryDate=BMCDeliveryDate,@OldDeliveryBillCode=DeliveryBillCode, 
@OldDeliveryFactDate=DeliveryFactDate,@OldRe=Re,@ModifyDate=getdate() 
FROM CSMS_SHOP_PartOrderBillSub 
WHERE OrderBillSubID=@OrderBillSubID   set  @OldDeliveryFactDate=convert(varchar(19),@OldDeliveryFactDate,120) --写在这里if  @IsAdmin=1    
 Begin   
  update CSMS_SHOP_PartOrderBillSub   
  set   
  BMCDeliveryDate=@BMCDeliveryDate,    
  DeliveryBillCode=@DeliveryBillCode,    
  DeliveryFactDate=@DeliveryFactDate, 
  OrderQty=@OrderQty,     
  SupplyPrice=@SupplyPrice,  
  TotalMoney=@TotalMoney, 
  Re=@Re, 
  IsDelete=@IsDelete, 
  PartNumber=@PartNumber, 
  CPartDesc=@CPartDesc 
  where  OrderBillSubID=@OrderBillSubID 
 End   
else   
 Begin   
  update CSMS_SHOP_PartOrderBillSub   
  set   
  BMCDeliveryDate=@BMCDeliveryDate,   
  DeliveryBillCode=@DeliveryBillCode,    
  DeliveryFactDate=@DeliveryFactDate, 
  OrderQty=@OrderQty,     
  SupplyPrice=@SupplyPrice,  
  TotalMoney=@TotalMoney, 
  Re=@Re, 
  IsDelete=@IsDelete, 
  PartNumber=@PartNumber, 
  CPartDesc=@CPartDesc   
  where  OrderBillSubID=@OrderBillSubID   
 End IF @OldPartNumber <>@PartNumber 
BEGIN 
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog 
@OrderBillCode=@OrderBillCode,  
@PartNumber=@OldPartNumber,  
@ModifyFieldName='零件代码',  
@ModifyLastValue=@OldPartNumber, 
@ModifyFinishValue=@PartNumber, 
@UserName=@OldUserName,  
@ModifyDate=@ModifyDate 
END IF @OldSupplyPrice <>@SupplyPrice 
BEGIN 
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog 
@OrderBillCode=@OrderBillCode,  
@PartNumber=@OldPartNumber,  
@ModifyFieldName='零件单价',  
@ModifyLastValue=@OldSupplyPrice, 
@ModifyFinishValue=@SupplyPrice, 
@UserName=@OldUserName,   
@ModifyDate=@ModifyDate 
END IF @OldOrderQty <>@OrderQty 
BEGIN 
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog 
@OrderBillCode=@OrderBillCode,  
@PartNumber=@OldPartNumber,  
@ModifyFieldName='订购数量',  
@ModifyLastValue=@OldOrderQty, 
@ModifyFinishValue=@OrderQty, 
@UserName=@OldUserName,    
@ModifyDate=@ModifyDate 
END IF @OldBMCDeliveryDate <>@BMCDeliveryDate 
BEGIN 
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog 
@OrderBillCode=@OrderBillCode,  
@PartNumber=@OldPartNumber,  
@ModifyFieldName='BMC预计发货日期',  
@ModifyLastValue=@OldBMCDeliveryDate, 
@ModifyFinishValue=@BMCDeliveryDate, 
@UserName=@OldUserName,   
@ModifyDate=@ModifyDate 
END IF @OldDeliveryBillCode <>@DeliveryBillCode 
BEGIN 
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog 
@OrderBillCode=@OrderBillCode,  
@PartNumber=@OldPartNumber,  
@ModifyFieldName='发货单号',  
@ModifyLastValue=@OldDeliveryBillCode, 
@ModifyFinishValue=@DeliveryBillCode, 
@UserName=@OldUserName,   
@ModifyDate=@ModifyDate 
END IF @OldDeliveryFactDate <>@DeliveryFactDate 
BEGIN 
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog 
@OrderBillCode=@OrderBillCode,  
@PartNumber=@OldPartNumber,  
@ModifyFieldName='实际发货日期',  
@ModifyLastValue=@OldDeliveryFactDate, 
@ModifyFinishValue=@DeliveryFactDate, 
@UserName=@OldUserName,    
@ModifyDate=@ModifyDate 
END 
IF @OldRe <>@Re 
BEGIN 
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog 
@OrderBillCode=@OrderBillCode,  
@PartNumber='',  
@ModifyFieldName='备注',  
@ModifyLastValue=@OldRe, 
@ModifyFinishValue=@Re, 
@UserName=@OldUserName,    
@ModifyDate=@ModifyDate 
END 

Answer 8

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*************************                        
功能名：网上商城：订单编辑，更新订单子表信息                     
创建人：吴永辉                       
创建日期：2007-8-1                        
**************************/  
ALTER       procedure [dbo].[CSMS_SHOP_UpdateOrderBillSub]  
@OrderBillSubID int,  
@BMCDeliveryDate smalldatetime,  
@DeliveryBillCode nvarchar(255), 
@DeliveryFactDate smalldatetime, 
@OrderQty int, 
@SupplyPrice money,
@TotalMoney money,  
@Re nvarchar(255),  
@IsDelete SmallInt,
@PartNumber nvarchar(255),
@CPartDesc Nvarchar(255),
@IsAdmin bit,
@OldUserName nvarchar(100)
as  declare @OrderBillCode  nvarchar(15)
declare @OldPartNumber  nvarchar(255)
declare @OldSupplyPrice money
declare @OldOrderQty    int
declare @OldBMCDeliveryDate smalldatetime
declare @OldDeliveryBillCode nvarchar(255)
declare @OldDeliveryFactDate smalldatetime
declare @OldRe       nvarchar(255)
declare @ModifyDate      SmallDateTime
SELECT @OrderBillCode=OrderBillCode
FROM CSMS_SHOP_PartOrderBillMain
WHERE OrderBillID=(SELECT OrderBillID FROM  CSMS_SHOP_PartOrderBillSub WHERE OrderBillSubID=@OrderBillSubID  )SELECT @OldPartNumber=PartNumber,@OldSupplyPrice=SupplyPrice,@OldOrderQty=OrderQty,
@OldBMCDeliveryDate=BMCDeliveryDate,@OldDeliveryBillCode=DeliveryBillCode,
@OldDeliveryFactDate=DeliveryFactDate,@OldRe=Re,@ModifyDate=getdate()
FROM CSMS_SHOP_PartOrderBillSub
WHERE OrderBillSubID=@OrderBillSubIDif  @IsAdmin=1   
 Begin  
  update CSMS_SHOP_PartOrderBillSub  
  set  
  BMCDeliveryDate=@BMCDeliveryDate,   
  DeliveryBillCode=@DeliveryBillCode,   
  DeliveryFactDate=@DeliveryFactDate,
  OrderQty=@OrderQty,    
  SupplyPrice=@SupplyPrice, 
  TotalMoney=@TotalMoney,
  Re=@Re,
  IsDelete=@IsDelete,
  PartNumber=@PartNumber,
  CPartDesc=@CPartDesc
  where  OrderBillSubID=@OrderBillSubID
 End  
else  
 Begin  
  update CSMS_SHOP_PartOrderBillSub  
  set  
  BMCDeliveryDate=@BMCDeliveryDate,  
  DeliveryBillCode=@DeliveryBillCode,   
  DeliveryFactDate=@DeliveryFactDate,
  OrderQty=@OrderQty,    
  SupplyPrice=@SupplyPrice, 
  TotalMoney=@TotalMoney,
  Re=@Re,
  IsDelete=@IsDelete,
  PartNumber=@PartNumber,
  CPartDesc=@CPartDesc  
  where  OrderBillSubID=@OrderBillSubID  
 End
set  @OldBMCDeliveryDate=convert(varchar(19),@OldBMCDeliveryDate,120)
set  @BMCDeliveryDate=convert(varchar(19),@BMCDeliveryDate,120)
set  @OldDeliveryFactDate=convert(varchar(19),@OldDeliveryFactDate,120)
set  @DeliveryFactDate=convert(varchar(19),@DeliveryFactDate,120)IF @OldPartNumber<>@PartNumber
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='零件代码', 
@ModifyLastValue=@OldPartNumber,
@ModifyFinishValue=@PartNumber,
@UserName=@OldUserName, 
@ModifyDate=@ModifyDate
ENDIF @OldSupplyPrice<>@SupplyPrice
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='零件单价', 
@ModifyLastValue=@OldSupplyPrice,
@ModifyFinishValue=@SupplyPrice,
@UserName=@OldUserName,  
@ModifyDate=@ModifyDate
ENDIF @OldOrderQty<>@OrderQty
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='订购数量', 
@ModifyLastValue=@OldOrderQty,
@ModifyFinishValue=@OrderQty,
@UserName=@OldUserName,   
@ModifyDate=@ModifyDate
ENDIF @OldBMCDeliveryDate<>@BMCDeliveryDate
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='BMC预计发货日期',
@ModifyLastValue=@OldBMCDeliveryDate,
@ModifyFinishValue=@BMCDeliveryDate,
@UserName=@OldUserName,  
@ModifyDate=@ModifyDate
ENDIF @OldDeliveryBillCode<>@DeliveryBillCode
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='发货单号', 
@ModifyLastValue=@OldDeliveryBillCode,
@ModifyFinishValue=@DeliveryBillCode,
@UserName=@OldUserName,  
@ModifyDate=@ModifyDate
ENDIF @OldDeliveryFactDate<>@DeliveryFactDate
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber=@OldPartNumber, 
@ModifyFieldName='实际发货日期', 
@ModifyLastValue=@OldDeliveryFactDate,
@ModifyFinishValue=@DeliveryFactDate,
@UserName=@OldUserName,   
@ModifyDate=@ModifyDate
ENDIF @OldRe<>@Re
BEGIN
   EXEC dbo.CSMS_SHOP_OrderBillModifyLog
@OrderBillCode=@OrderBillCode, 
@PartNumber='', 
@ModifyFieldName='备注', 
@ModifyLastValue=@OldRe,
@ModifyFinishValue=@Re,
@UserName=@OldUserName,   
@ModifyDate=@ModifyDate
END