SQL Server 2008 and time(0)
Posted: Tue 20 Jul 2010 07:06
Hi,
This is using SQL Server 2008, D2010 and UniDAC 3.00.0.10
I have the following table definition:
When I use a {date... and {time.. macros, the generated SQL for a particular update is:
However, this raises an error on the last comparison:
( JOB_TIME = CONVERT(DATETIME, '17:00:00') )
error:
"The data types time and datetime are incompatible in the equal to operator"
I have worked around it for now by changing the following functions in the SQLServerUniProvider:
I realize this may not be compatible with earlier versions of SQL server but I wonder if you are able to detect the version or add an option to change the function to produce DATE or TIME instead of DATETIME ?
Regards, Paul.
This is using SQL Server 2008, D2010 and UniDAC 3.00.0.10
I have the following table definition:
Code: Select all
CREATE TABLE JOBS
(
JOB_ID int NOT NULL,
JOB_DATE date NOT NULL,
JOB_TIME time(0) NOT NULL, --also same error with time(7)
PER_ID int NOT NULL,
CONSTRAINT PK_JOBS PRIMARY KEY ( JOB_ID ASC )
)When I use a {date... and {time.. macros, the generated SQL for a particular update is:
Code: Select all
UPDATE JOBS SET JOB_DATE = CONVERT(DATETIME, '2010-07-20'), JOB_TIME = CONVERT(DATETIME, '17:05:00')
WHERE ( JOB_ID = 2913525 )
AND ( JOB_DATE = CONVERT(DATETIME, '2010-07-20') )
AND ( JOB_TIME = CONVERT(DATETIME, '17:00:00') )
AND ( PER_ID = 181412 )( JOB_TIME = CONVERT(DATETIME, '17:00:00') )
error:
"The data types time and datetime are incompatible in the equal to operator"
I have worked around it for now by changing the following functions in the SQLServerUniProvider:
Code: Select all
MSFunctions.AddObject('__DATE_LITERAL',TObject(PChar('CONVERT(DATE, %s)')));
MSFunctions.AddObject('__TIME_LITERAL',TObject(PChar('CONVERT(TIME, %s)')));Regards, Paul.