To me it just seems to me a matter of data types. Is date_history really a character type field?
Now minus one day is [tt]dateadd(day, getdate(),-1)[/tt]
If DATE_HISTORY is a datetime, you very very very very very rarely will get a match of DATE_HISTORY and that timestamp now minus one day,
so you have two options:
1. You convert both datetimes to a date and compare them, which makes a fit of any two times of yesterday.
2. You convert yesterdays datetime (now-1 day) to the timespan of yesterday 0 AM to today 0 AM and check, whether DATE_HISTORY is between these start and end datetimes.
I would vote for 1, even more so, if DATE_HISTORY is a date itself already.
1.1 Yesterdays date is [tt]cast(dateadd(day, getdate(),-1) as date)[/tt]
1.2 DATE_HISTORYs date is either itself or [tt]cast(DATE_HISTORY as date)[/tt], the latter will work anyway, because casting a date as date is unneccessary, but also doesn't harm.
So the comparison you could do, if DATE_HISTORY is some of the many date/datetime types is
[tt]cast(DATE_HISTORY as date) = cast(dateadd(day, getdate(),-1) as date)[/tt]
If you want to write queries, the first look has to be about the data types, especially when unsure about such comparisons. The second thought about non matching types is which to convert and how to compare. Before we really had date in MSSQL you could only go with option 2 to test a datetime is within a timespan. Yesterday and any date is in fact a timespan.
If DATE_HISTORY really is a character type, then sorry for that excursion, I don't think it'll hurt, though. The best idea then would still be converting the DATE_HISTORY to a date or datetime instead of converting a date to a character string.
Don't judge a book by it's cover, any query tool, aslo SSMS will show you a date or datetime column in some humany readable locale format with year, month, day, hours, minutes, seconds and perhaps AM/PM. But stored in a database is a binary value. A number also isn't stored as the decimal digits you see displayed or written in code, it is encoded in 4 byte for a normal integer, 4 bytes which displayed as ascii or unicode characters would just look as garbage. But the human readable format you get displayed in a query tool result grid/listview is just human readable, its never your goal to get other data looking like that to be able to compare it, in code you compare the data in their machine format, you also compare wo integers by eg int1=int2 and not CAST(int1 as varchar(11)) = CAST(int2 as varchar(11)), why would you first convert 4 bytes to up to 10 digits and a sign each, to then compare these string representations of the numbers?
We need at least one cast here, for the getdate() side, as getdate() unlike the name it suggests is a datetime and not a date. Sidenote: there always has been CURRENT_TIMESTAMP. Aside of that unavoidable cast because of a missing function to return just the current days date, you also only need to cast DATE_HISTORY as a date, if it isn't one. And in this case it's not a cast without a reason, like with the integer to character casting, you need to strip off time.
Bye, Olaf.