SQL Server 2008 and time(0)

[hughespa]

Hi,

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 )

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:

Code: Select all

MSFunctions.AddObject('__DATE_LITERAL',TObject(PChar('CONVERT(DATE, %s)')));

MSFunctions.AddObject('__TIME_LITERAL',TObject(PChar('CONVERT(TIME, %s)')));

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.

[Dimon]

Thank you for information. We have fixed this problem. This fix will be included in the next UniDAC build.

[hughespa]

Great, thank you.

Regards, Paul.