Query fails on "converting character string to smalldatetime data type"

Question

I've been tasked with fixing some SQL code that doesn't work. The query reads from a view against a predicate. The query right now looks like so.

SELECT  TOP (100) Beginn
FROM  V_LLAMA_Seminare
//Removal of the following line makes the query successful, keeping it breaks it
where Beginn  > (select cast (getdate() as smalldatetime))
order by Beginn desc

When I run the above query, I am greeted with the following error.

Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.

I decided to remove the WHERE clause, and now it runs returning 100 rows.

At first, I thought that behind the scenes, SQL Server was somehow including my predicate when bringing back the View . But then I investigated how the View was being created, especially the Beginn field, and at no point does it return a String.

Long story short, the column that becomes the Beginn field is a BIGINT timestamp like 201604201369.... The original user transforms this BIGINT to a smalldatetime using the following magic.

....
CASE WHEN ma.datum_dt = 0 
   THEN null 
   ELSE CONVERT(smalldatetime, SUBSTRING(CAST(ma.datum_dt AS varchar(max)),0,5) + '-' + 
   SUBSTRING(CAST(ma.datum_dt AS varchar(max)),5,2)  + '-' + 
   SUBSTRING(CAST(ma.datum_dt AS varchar(max)),7,2) +  ' ' + 
   SUBSTRING(CAST(ma.datum_dt AS varchar(max)),9,2) +':'+
   SUBSTRING(CAST(ma.datum_dt AS varchar(max)),11,2) +':' + 
   RIGHT(CAST(ma.datum_dt AS varchar(max)),2)) END AS Beginn
...

My last attempt at finding the problem was to query the view and run the function ISDATE over the Beginn column and see if it returned a 0 which it never did.

So my question is two fold, "Why does a predicate break something" and two "Where on earth is this string error coming from when the Beginn value is being formed from a BIGINT".

Any help is greatly appreciated.

This might be related: http://stackoverflow.com/q/36542569/5089204 — Shnugo, Apr 28 '16 at 07:47

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

This problem is culture related...

Try this and then change the first SET LANGUAGE to GERMAN

SET LANGUAGE ENGLISH;
DECLARE @bi BIGINT=20160428001600;
SELECT CASE WHEN @bi = 0 
   THEN null 
   ELSE CONVERT(datetime, SUBSTRING(CAST(@bi AS varchar(max)),0,5) + '-' + 
   SUBSTRING(CAST(@bi AS varchar(max)),5,2)  + '-' + 
   SUBSTRING(CAST(@bi AS varchar(max)),7,2) +  ' ' + 
   SUBSTRING(CAST(@bi AS varchar(max)),9,2) +':'+
   SUBSTRING(CAST(@bi AS varchar(max)),11,2) +':' + 
   RIGHT(CAST(@bi AS varchar(max)),2)) END AS Beginn

It is a very bad habit to think, that date values look the same everywhere (Oh no, my small application will never go international ...)

Try to stick to culture independent formats like ODBC or ISO

EDIT

A very easy solution for you actually was to replace the blank with a "T"

   SUBSTRING(CAST(ma.datum_dt AS varchar(max)),7,2) +  'T' +

Then it's ISO 8601 and will convert...

It didn't work. I'm thinking that the server is doing some kind of implicit conversion but I don't have enough privileges for SHOWPLAN ='( (and I'll probably never get them). — Black Dynamite, Apr 28 '16 at 03:40

score 0 · Answer 2 · answered Apr 29 '16 at 20:20

The solution was found after looking through @Shnugo's comment. When I took my query which contained the Bigint->Datetime conversion logic, and put it into a CTE with "TOP 100000000" to avoid any implicit conversion actions, my query worked. Here is what my view looks like now with some unimportant parts omitted.

---Important part---
CREATE VIEW [dbo].[V_SomeView] AS
WITH CTE AS (
SELECT TOP 1000000000 ma.id AS MA_ID,
---Important part---
   vko.extkey AS ID_VKO,
   vko.text AS Verkaufsorganisation,
   fi.f7000 AS MDM_Nr,
   vf.f7105 AS SAPKdnr,
   CASE WHEN ma.datum_dt = 0 --Conversion logic
   CASE WHEN ma.endedatum_dt = 0 --Conversion logic
   CONVERT(NVARCHAR(MAX),art.text) AS Art,
    .....
FROM [ucrm].[dbo].[CRM_MA] ma,
[ucrm].[dbo].[CRM_fi] fi,
[ucrm].[dbo].[CRM_vf] vf,
[ucrm].[dbo].[CRM_ka] vko,
[ucrm].[dbo].[CRM_ka] art,
[ucrm].[dbo].[CRM_ka] kat
where ma.loskz = 0
and fi.loskz = 0
and vf.loskz = 0
and fi.F7029 = 0
and vf.F7023 = 0
...
GROUP BY ma.id,
   vko.extkey,
   vko.text,
   fi.f7000 ,
   vf.f7105,
   ma.datum_dt,
   ma.endedatum_dt,
   ....
)
select * FROM CTE;

Query fails on "converting character string to smalldatetime data type"

2 Answers2

EDIT