Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL 3
COLUMNPROPERTY 1
OBJECT_NAME 1
OBJECT_ID 1
SysColumns 1
IF EXISTS 1
decimal 1
float 1
conversion error 1
implicit convertion 1

Archive · View All
March 2012 3
April 2012 2

Adam Tokarski's Blog

Under hood date convertions errors

Mar 29 2012 12:00AM by Adam Tokarski   

Disclaimer: It may be outdated, according to low server version, but still happens.

What is about?

Some time ago, in our application has shown some convert, quite strange, error. I will start from the beginning. At first, for sake of example, create table:

 IF OBJECT_ID('Table1') IS NOT NULL 
 DROP TABLE [Table1] 
 GO 

 IF OBJECT_ID('Table1') IS NULL 
 CREATE TABLE [Table1] 
  ( 
   [Table1Id] int NOT NULL IDENTITY(1, 1),
   [dateFrom] datetime,
   [dateTo] datetime,
   [dateException] datetime
  )
 GO 

then fill it with some data:

INSERT INTO [Table1] (dateFrom,dateTo,dateException) VALUES ('20080714','20101231','20100330')
INSERT INTO [Table1] (dateFrom,dateTo,dateException) VALUES ('20110101','20111231','20110630')
INSERT INTO [Table1] (dateFrom,dateTo,dateException) VALUES ('20120101','20121231','20120630')
INSERT INTO [Table1] (dateFrom,dateTo,dateException) VALUES ('20130101','20131231','20130630')
INSERT INTO [Table1] (dateFrom,dateTo,dateException) VALUES ('20140101','20141231','20140630')

It seems that it is relevant:

SET LANGUAGE 'polish' 
or
SET LANGUAGE 'german' 

As you can see, it's just an extracted, and simplified version of some production table.

Lets play!

As first we try that queries:

SELECT *
FROM [Table1] 
WHERE {ts '2010-06-30 00:00:00.000'} between dateFrom and dateTo
and
SELECT CASE WHEN {ts '2010-06-30 00:00:00.000'} > dateException THEN 1 ELSE 0 END [NextPeriod]
FROM [Table1] 

that works without any problems.

But now merge them into one query...

SELECT CASE WHEN {ts '2010-06-30 00:00:00.000'} > dateException THEN 1 ELSE 0 END [NextPeriod]
FROM [Table1] 
WHERE {ts '2010-06-30 00:00:00.000'} between dateFrom and dateTo 

BUMP!

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

For me, it's quite strange. When you run it at higher version of sql, or change language to english, or when just change

{ts '2010-06-30 00:00:00.000'}
to ISOs:
'20100630'
it simply does work.

Unfortunatelly, in my job, that writing of date (as ODBC API) come from client application, we use only polish or german language versions, and - and it hurts the most - we still work at server 2005 (to dig to bottom of hell, for some purpose, at compatibility level of 80...).

I was trying to work it out, but it seems that compiler is making implicit conversion of date in WHERE clause, but avoid it (somehow) at CASE in WHERE clause. So I must force to cast of date (of rather proper wrote ODBC timestamp standard!) to DATETIME.

SELECT CASE WHEN CAST({ts '2010-06-30 00:00:00.000'} AS DATETIME) > dateException THEN 1 ELSE 0 END [NextPeriod]
FROM [Table1] 
WHERE {ts '2010-06-30 00:00:00.000'} between dateFrom and dateTo  
Or just make it, at application stage yet, to send data as ISO standard (which seems for to be best options, and safest at all):
SELECT CASE WHEN '20100630' > dateException THEN 1 ELSE 0 END [NextPeriod]
FROM [Table1] 
WHERE '20100630' between dateFrom and dateTo  

Thank you for your attention.
-- Adam Tokarski --

Tags: sql, date convertion, implicit convertion, conversion error


Adam Tokarski
58 · 3% · 1021
6
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Under hood date convertions errors" rated 5 out of 5 by 6 readers
Under hood date convertions errors , 5.0 out of 5 based on 6 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]