列名称'00：00'无效。 [英] Invalid column name '00:00'.

问题描述

当我运行此sql时，它始终显示无效列'00 .00'。请你帮我解决这个问题。错误来自case语句

，Case当DateClockedIn为null然后"00:00"时Else CONVERT（VARCHAR（5），DateClockedIn，108）以DateClockedIn 
结束，Case当DateClockedOut为null然后"00:00"。 Else CONVERT（VARCHAR（5），DateClockedOut，108）以DateClockedOut结束

 SET @QRY ='SELECT 
 DATEPART（MM，[AttendanceDate]）AS月
，LogID 
，log。[DepotNo] 
，log。[ DepartmentID] 
，employee.EmployeeID 
，employee.EmployeeNo 
，employee.EmployeeName 
，CONVERT（VARCHAR（10），[AttendanceDate]，103）AS Date 
 ，CONVERT（VARCHAR（8），[AttendanceDate]，4）AS AttendanceDate 
，[IsNormalAttendance] AS Normal 
，[IsDayOffMarked] AS DayOff 
，[IsFuneralDayOff] AS Funeral 
 ，[IsSickMarked] AS Sick 
，[IsHolidayMarked] AS Holiday 
，[IsMaternityLeave] AS Maternity 
，[IsTimeOff] AS TimeOff 
，[TimeOffHrs] 
，[ IsDeductionMarked] 
，CASE WHEN [IsDeductionMarked] = 1 THEN [dbo]。[goGetEmployeeNetHrsForDate]（employee.EmployeeID，AttendanceDate）ELSE 0.00 END Deduct 
，[ExtraTimeHrs] 
，[备注] 
，Case Wh en DateClockedIn为null然后"00:00"。 Else CONVERT（VARCHAR（5），DateClockedIn，108）以DateClockedIn 
结束，Case当DateClockedOut为null然后"00:00"。 Else CONVERT（VARCHAR（5），DateClockedOut，108）以DateClockedOut结尾
 
 FROM [dbo]。[goAttendancelog] log 
 INNER JOIN dbo.goEmployee employee 
 ON log.DepotNo = employee.DepotNo 
和log.DepartmentID = employee.DepartmentID 
和employee.EmployeeID = log.EmployeeID 
 WHERE 
 employee.EmployeeID in（'+ @ Employee +'）$ b 'b'和attendanceDate'''+ convert（varchar（10），@ FromDate，101）+'''和'''+ convert（varchar（10），@ ToDate，101）+'''
按employee.EmployeeName，DATEPART（MM，[AttendanceDate]），AttendanceDate'排序; 
 PRINT @QRY; 
 EXEC（@QRY）; 

---

polachan

解决方案

Hi polachan，

您应该使用单引号而不是双引号

  - ，DateClockedIn为null时的情况然后是"00:00" Else CONVERT（VARCHAR（5），DateClockedIn，108）以DateClockedIn 
结束 - ，当DateClockedOut为空时的情况然后是"00:00"。 Else CONVERT（VARCHAR（5），DateClockedOut，108）以DateClockedOut结尾
 
  - 更新
，Case当DateClockedIn为null然后''00：00''Else CONVERT（VARCHAR（5） ），DateClockedIn，108）结束为DateClockedIn 
，Case当DateClockedOut为null然后''00：00''Else CONVERT（VARCHAR（5），DateClockedOut，108）结束为DateClockedOut 
 
 SET @QRY ='SELECT 
 DATEPART（MM，[AttendanceDate]）AS月
，LogID 
，log。[DepotNo] 
，log。[DepartmentID] 
 ，employee.EmployeeID 
，employee.EmployeeNo 
，employee.EmployeeName 
，CONVERT（VARCHAR（10），[AttendanceDate]，103）AS Date 
，CONVERT（VARCHAR（8） ），[出勤日期]，4）AS出勤日期
，[IsNormalAttendance] AS正常
，[IsDayOffMarked] AS DayOff 
，[IsFuneralDayOff] AS Funeral 
，[IsSickMarked] AS Sick 
，[IsHolidayMarked] AS Holiday 
，[IsMaternityLeave] AS Maternity 
，[IsTimeOff] AS TimeOff 
，[TimeOffHrs] 
，[IsDeductionMarked] 
，CASE WHEN [IsDeductionMarked] = 1 THEN [dbo]。[goGetEmployeeNetHrsForDate]（employee.EmployeeID，AttendanceDate）ELSE 0.00 END Deduct 
，[ExtraTimeHrs] 
 ，[备注] 
，Case当DateClockedIn为null然后''00：00''Else CONVERT（VARCHAR（5），DateClockedIn，108）结束为DateClockedIn 
，Case当DateClockedOut为null然后'' 00:00''Else CONVERT（VARCHAR（5），DateClockedOut，108）以DateClockedOut结尾
 
 FROM [dbo]。[goAttendancelog] log 
 INNER JOIN dbo.goEmployee employee 
 ON log.DepotNo = employee.DepotNo 
和log.DepartmentID = employee.DepartmentID 
和employee.EmployeeID = log.EmployeeID 
 WHERE 
 employee.EmployeeID in（'+ @员工+'）
和'''+转换（varchar（10），@ FromDate，101）+'''和'''+转换（varchar（10），@ ToDate，101）+''之间的AttendanceDate '
由employee.EmployeeName，DATEPART（MM， [AttendanceDate]），AttendanceDate"; 
 PRINT @QRY; 
 EXEC（@QRY）; 

最好的问候，

将

When I run this sql it always shows Invalid column '00.00'. Please can you help me to fix this. The error is coming in case statement

  ,Case When DateClockedIn is null Then "00:00" Else CONVERT(VARCHAR(5),DateClockedIn,108) End as DateClockedIn
	  ,Case When DateClockedOut is null Then "00:00" Else CONVERT(VARCHAR(5),DateClockedOut,108) End as DateClockedOut

SET @QRY = 'SELECT  
	  DATEPART(MM, [AttendanceDate]) AS Month
	  ,LogID 
      ,log.[DepotNo]
      ,log.[DepartmentID]
	  ,employee.EmployeeID
	  ,employee.EmployeeNo
	  ,employee.EmployeeName
	  ,CONVERT(VARCHAR(10), [AttendanceDate], 103) AS Date
	  ,CONVERT(VARCHAR(8), [AttendanceDate], 4) AS AttendanceDate
	  ,[IsNormalAttendance] AS Normal
	  ,[IsDayOffMarked] AS DayOff
	  ,[IsFuneralDayOff] AS Funeral
	  ,[IsSickMarked] AS Sick
	  ,[IsHolidayMarked] AS Holiday
	  ,[IsMaternityLeave] AS Maternity
	  ,[IsTimeOff] AS TimeOff
	  ,[TimeOffHrs]
	  ,[IsDeductionMarked]
	  ,CASE WHEN [IsDeductionMarked] = 1 THEN [dbo].[goGetEmployeeNetHrsForDate](employee.EmployeeID,AttendanceDate) ELSE 0.00 END Deduct
	  ,[ExtraTimeHrs]
      ,[Remark]
	  ,Case When DateClockedIn is null Then "00:00" Else CONVERT(VARCHAR(5),DateClockedIn,108) End as DateClockedIn
	  ,Case When DateClockedOut is null Then "00:00" Else CONVERT(VARCHAR(5),DateClockedOut,108) End as DateClockedOut
	 
      FROM [dbo].[goAttendancelog] log
		  INNER JOIN dbo.goEmployee employee	
		  ON  log.DepotNo = employee.DepotNo
			   and  log.DepartmentID = employee.DepartmentID	
			   and    employee.EmployeeID = log.EmployeeID
      WHERE 
		    employee.EmployeeID in ('+@Employee+')
		  and AttendanceDate  between '''+convert(varchar(10),@FromDate,101)+''' and '''+ convert(varchar(10),@ToDate,101)+''' 
	  order by employee.EmployeeName,DATEPART(MM, [AttendanceDate]),AttendanceDate';
	  PRINT @QRY;
	  EXEC(@QRY);

---

polachan

解决方案

Hi polachan,

You should use single quotation mark instead of double quotation marks

  --,Case When DateClockedIn is null Then "00:00" Else CONVERT(VARCHAR(5),DateClockedIn,108) End as DateClockedIn
	 -- ,Case When DateClockedOut is null Then "00:00" Else CONVERT(VARCHAR(5),DateClockedOut,108) End as DateClockedOut

--Update
	 ,Case When DateClockedIn is null Then ''00:00'' Else CONVERT(VARCHAR(5),DateClockedIn,108) End as DateClockedIn
	  ,Case When DateClockedOut is null Then ''00:00'' Else CONVERT(VARCHAR(5),DateClockedOut,108) End as DateClockedOut

	  SET @QRY = 'SELECT  
	  DATEPART(MM, [AttendanceDate]) AS Month
	  ,LogID 
      ,log.[DepotNo]
      ,log.[DepartmentID]
	  ,employee.EmployeeID
	  ,employee.EmployeeNo
	  ,employee.EmployeeName
	  ,CONVERT(VARCHAR(10), [AttendanceDate], 103) AS Date
	  ,CONVERT(VARCHAR(8), [AttendanceDate], 4) AS AttendanceDate
	  ,[IsNormalAttendance] AS Normal
	  ,[IsDayOffMarked] AS DayOff
	  ,[IsFuneralDayOff] AS Funeral
	  ,[IsSickMarked] AS Sick
	  ,[IsHolidayMarked] AS Holiday
	  ,[IsMaternityLeave] AS Maternity
	  ,[IsTimeOff] AS TimeOff
	  ,[TimeOffHrs]
	  ,[IsDeductionMarked]
	  ,CASE WHEN [IsDeductionMarked] = 1 THEN [dbo].[goGetEmployeeNetHrsForDate](employee.EmployeeID,AttendanceDate) ELSE 0.00 END Deduct
	  ,[ExtraTimeHrs]
      ,[Remark]
	  ,Case When DateClockedIn is null Then ''00:00'' Else CONVERT(VARCHAR(5),DateClockedIn,108) End as DateClockedIn
	  ,Case When DateClockedOut is null Then ''00:00'' Else CONVERT(VARCHAR(5),DateClockedOut,108) End as DateClockedOut
	 
      FROM [dbo].[goAttendancelog] log
		  INNER JOIN dbo.goEmployee employee	
		  ON  log.DepotNo = employee.DepotNo
			   and  log.DepartmentID = employee.DepartmentID	
			   and    employee.EmployeeID = log.EmployeeID
      WHERE 
		    employee.EmployeeID in ('+@Employee+')
		  and AttendanceDate  between '''+convert(varchar(10),@FromDate,101)+''' and '''+ convert(varchar(10),@ToDate,101)+''' 
	  order by employee.EmployeeName,DATEPART(MM, [AttendanceDate]),AttendanceDate';
	  PRINT @QRY;
	  EXEC(@QRY);

Best Regards,

Will

这篇关于列名称'00：00'无效。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！