从字符串转换日期和/或时间时问题转换失败 [英] Problem Conversion failed when converting date and/or time from character string

问题描述

Plz解决了这个问题

Plz solve this problem

alter PROCEDURE [dbo].[sp_ShowTimetable]   
(
 @date datetime
)
as
begin


DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);


SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(Period_Name)
  FROM (Select Period_Name from tbl_TimetablePeriod where Active=1) AS x;

 select @cols

SET @query= 'Select Location_Name,'+@cols+'
from
(
 Select  temp2.Course_Name+'', '' + temp2.CLASSNAME+'', ''+temp2.TEACHER as TimeTableClass, temp2.Period_Name,TL.Location_Name  from

  (Select  TT.*,TC.Course_Name,TB.Name AS CLASSNAME,TE.First_Name +'' ''+ ISNULL(TE.Last_Name,'''') AS TEACHER,TP.Period_Name
   from tbl_timetable TT JOIN tbl_Course TC ON TT.CourseId=TC.Course_Id
   JOIN tbl_Batch TB on TT.ClassId=TB.Batch_Id
   JOIN tbl_Employees TE on TT.empid=TE.empid
   JOIN tbl_Timetableperiod TP on TT.periodid=TP.Period_Id Where TT.date= convert(datetime,''+@date+'',121))


 as temp2 Right Outer Join  tbl_timetablelocation TL ON temp2.Loactionid =TL.Location_Id
) src
pivot
(
  max(TimeTableClass)
  for Period_Name in ('+@cols+')
) piv'

Exec(@query)

select @query


End

执行： - [sp_ShowTimetable] '04 / 06/2015'

execute :- [sp_ShowTimetable] '04/06/2015'

推荐答案

你在这里做的很奇怪！ @DATE是一个日期时间所以为什么要将它变成字符串并尝试将其解析回日期时间???

我相信这种混乱的根源是你不明白日期时间是一个二进制忽略任何格式化的格式以及你在SSMS中看到的显示格式 - 只有显示格式......

所有你需要的是删除不必要的转换并将日期与日期比较...在二进制基础上...

You do here something very strange! @DATE IS a datetime so why to turn it into string and try to parse it back to datetime???
I believe that the origin for that confusion is that you do not understand that datetime is a BINARY format that neglect any formating and the display format you see in SSMS is - well a display format only...
All you need is drop that unnecessary converting and compare date with date...on binary base...

WHERE TT.DATE = @DATE

这篇关于从字符串转换日期和/或时间时问题转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！