How does Max() work with smalldatetime.

[FatalExceptionError]

I have something similar to this.

declare @last_date_read smalldatetime

select @last_date_read = max(date_time) from database_A

SELECT somestuff..., [time], date, more stuff 
FROM
OPENQUERY(somelinkedserver,'Select * from databaseB) 
WHERE CONVERT(smalldatetime, (date+' '+[time in]))> @last_date_read

When I do this i get records returned for all things with a date that is on that same day rather than the ones occuring after the specified time on that day.

Am I wrong in assuming that max(smalldatetime) takes into account date as well as time.



=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[vongrunt]

More info please - about linked server itself, aabout data types for date and [time in] columns etc.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[FatalExceptionError]

the linked server is a paradox DB

date and [time in] are varchars for some reason.

According to my outputs everything is in the correct format

i.e. "2005-12-15 09:03:00"


=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[vongrunt]

Are you sure @last_date_read holds both date and time? What happens if you hardcode that value, for example:

-- select @last_date_read = max(date_time) from database_A
set @last_date_read = '2005-12-15 08:30:05'

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[FatalExceptionError]

Same results come up when I do that. Its getting to be annoying. The MSDN T-SQL reference does not give much detail about how things work and are evaluated. I need another source. Just wish I knew how it evaluates the date format.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[jbenson001]

Are you sure @last_date_read is a smalldatatime? If it is actually a datetime, this could cause issues.


Jim

 

[FatalExceptionError]

Yes it is smalldatetime. I declared it as such. I am beginning to think it's a flaw in my logic and order of operations.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[FatalExceptionError]

Yes it was simply changing the where clause to this fixed the issue.

WHERE CONVERT(smalldatetime, (date+' '+[time in])) > @last_date_read 
and (customer = 'sl' or customer = 'hl' or customer = 'tb' )

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[FatalExceptionError]

Cripes triple posting but I neglected to thank everybody for helping me.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[jbenson001]

Glad its working..
Jim