Quirky Min and Max DATETIME

[Hexonx]

I'm working with a date range query that has optional start and end dates. When not input, I set the start or end date to the minimum or maximum value allowed for datetime types.

Being new to Sybase, I tested this functionality with the following procedure:

DECLARE @dtMin datetime, @dtMax datetime

SELECT @dtMin = '1/1/1753 00:00:00'
SELECT @dtMax = '12/31/9999 23:59:59'

SELECT CONVERT(varchar, @dtMin) 'Min VC', 
	   CONVERT(datetime, @dtMin) 'Min DT',
	   CONVERT(varchar, @dtMax) 'Max VC', 
	   CONVERT(datetime, @dtMax) 'Max DT'

This is the output (comma-delimited):

Jan  1 1753 12:00AM, 01/21/11760974 00:00:00:000, Dec 31 9999 11:59PM, 01/01/1900 00:00:00.000

It looks like the datetime fields in the result set are being implicitly converted to smalldatetime, despite the explicit CONVERT(datetime, ...). My query tool is CAST AMS 4.0 for SQL Server.

Any comments?

Craig Boland

 

[mikewoodhouse]

It could be something to do with CAST or something peculiar about the version of Sybase. Running your code in DB Artisan on 11.9.2 gives the answers you were looking for, which doesn't help much! Sorry...