title: SQL Server : error converting string to smalldatetime?
body: <p>Can anyone help me with the following extract of code I have put together. The error appears to be in the <code>CASE</code> statement but I'm unable to see where as all the code parts after THEN have been tested individually and work.</p>
  
  <p>Also to note that all dates are in <code>datetime</code> data type being <code>yyyy-mm-dd hh:mm:ss.sss</code></p>
  
  <pre><code>SELECT 
      TICKET_ID,
      CAL_CR.a_DayName,
      CREATED, 
      CAL_CL.a_DayName,
      CLOSED,
      CASE
          WHEN CAL_CR.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NULL THEN (DATEDIFF(WEEKDAY,created, closed)*10*60 - (DATEDIFF(MINUTE,CURRENT_TIMESTAMP,CAST(CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS VARCHAR) + ' 18:00' AS DATETIME))))
          WHEN CLOSED IS NULL THEN (DATEDIFF(WEEKDAY,created, closed)*10*60 - (DATEDIFF(MINUTE,CAST(CAST(CAST(CREATED AS DATE) AS VARCHAR) + ' 08:30' AS DATETIME),CREATED)) - (DATEDIFF(MINUTE,CURRENT_TIMESTAMP,CAST(CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS VARCHAR) + ' 18:00' AS DATETIME))))
          WHEN CAL_CR.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NOT NULL THEN (DATEDIFF(WEEKDAY,created, closed)*10*60 - (DATEDIFF(MINUTE,CLOSED,CAST(CAST(CAST(CLOSED AS DATE) AS VARCHAR) + ' 18:00' AS DATETIME))))
          WHEN CAL_CL.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NOT NULL THEN (DATEDIFF(WEEKDAY,created, closed)*10*60 - (DATEDIFF(MINUTE,CAST(CAST(CAST(CREATED AS DATE) AS VARCHAR) + ' 08:30' AS DATETIME),CREATED)))
          WHEN (CAL_CR.A_DATE IN ('Saturday','Sunday')) AND (CAL_CL.A_DATE IN ('Saturday','Sunday')) AND CLOSED IS NOT NULL THEN (DATEDIFF(WEEKDAY,created, closed)*10*60)
          ELSE (DATEDIFF(WEEKDAY,created, closed)*10*60 - (DATEDIFF(MINUTE,CAST(CAST(CAST(CREATED AS DATE) AS VARCHAR) + ' 08:30' AS DATETIME),CREATED)) - (DATEDIFF(MINUTE,CLOSED,CAST(CAST(CAST(CLOSED AS DATE) AS VARCHAR) + ' 18:00' AS DATETIME))))
      END AS Open_Minutes
  FROM 
      OST_TICKET 
  LEFT JOIN
      AMI_STATIC.DBO.SB_CALENDAR CAL_CR
      ON
          CAL_CR.a_Date = CAST(CREATED AS DATE)
  LEFT JOIN
      AMI_STATIC.DBO.SB_CALENDAR CAL_CL
      ON
          CAL_CL.a_Date = CAST(CLOSED AS DATE)
  </code></pre>
  
  <p>Many Thanks in advance for any help at all.</p>
  
  <hr>
  
  <p>Thanks Dems,</p>
  
  <p>Taken your suggestions and tidied the code up a little further. See Below:</p>
  
  <pre><code>SELECT 
      TICKET_ID,
      CAL_CR.a_DayName,
      CREATED, 
      CAL_CL.a_DayName,
      CLOSED,
      CASE
          WHEN CAL_CR.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NULL THEN DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST(CURRENT_TIMESTAMP AS TIME),CAST('18:00' AS TIME))
          WHEN CLOSED IS NULL THEN DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST('08:30' AS TIME),CAST(CREATED AS TIME)) - DATEDIFF(MINUTE,CAST(CURRENT_TIMESTAMP AS TIME),CAST('18:00' AS TIME))
          WHEN CAL_CR.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NOT NULL THEN DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST(CLOSED AS TIME),CAST('18:00' AS TIME))
          WHEN CAL_CL.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NOT NULL THEN DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST('08:30' AS TIME),CAST(CREATED AS TIME))
          WHEN (CAL_CR.A_DATE IN ('Saturday','Sunday')) AND (CAL_CL.A_DATE IN ('Saturday','Sunday')) AND CLOSED IS NOT NULL THEN DATEDIFF(WEEKDAY,created, closed)*10*60
          ELSE DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST('08:30' AS TIME),CAST(CREATED AS TIME)) - DATEDIFF(MINUTE,CAST(CLOSED AS TIME),CAST('18:00' AS TIME))
      END AS Open_Minutes
  FROM 
      OST_TICKET 
  LEFT JOIN
      AMI_STATIC.DBO.SB_CALENDAR CAL_CR
      ON
          CAL_CR.a_Date = CAST(CREATED AS DATE)
  LEFT JOIN
      AMI_STATIC.DBO.SB_CALENDAR CAL_CL
      ON
          CAL_CL.a_Date = CAST(CLOSED AS DATE)
  </code></pre>
  
  <p>Still getting the same error though:</p>
  
  <p><em>Conversion failed when converting character string to smalldatetime data type.</em></p>
  
  <p>Which is starting to get a little annoying.</p>
  
  <p>Taking the code apart still works as well </p>
  
  <pre><code>SELECT 
      DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST('08:30' AS TIME),CAST(CREATED AS TIME)) - DATEDIFF(MINUTE,CAST(CLOSED AS TIME),CAST('18:00' AS TIME))
  FROM 
      OST_TICKET
  WHERE 
      TICKET_ID = 30374
  </code></pre>
  
  <p>Thanks,</p>
  
  <p>Marcus</p>