I am Extraxting Data from Teradata using the two scripts below which are supposedly the same but Syntax 1) returns less than 2), i need to understand what could be causing the variance or if it's both the correct way to do it or not. please assist!
1. SELECT COUNT(1)
WHERE DATE_START_HIST<= Cast(Current_date as Date) - (Extract (Day from Current_date))
AND DATE_END_HIST > Cast(Current_date as Date) - (Extract (Day from Current_date))
Results: 23 028 717
2. SELECT COUNT(1)
WHERE DATE_START_HIST <='2013-06-30 00:00:00.000'
AND DATE_END_HIST > '2013-06-30 00:00:00.000'
Results: 23 028 732
Both conditions are not exactly the same.
I assume those DATE_START/END_HIST columns are TIMESTAMPs (#2 should fail for DATEs).
#1: Compares a TIMESTAMP to a DATE, resulting in the TIMESTAMP is implictly CASTed to a DATE and thus the TIME part is truncated.
#2: Compares a TIMESTAMP with a literal, which is resolved to a timestamp by the parser (better write TIMESTAMP '2013-06-30 00:00:00.000' instead).
And there might be some TimeZones involved...
You should check both explains to see the actual comparison.
Thank you very much to you, i see what, perhaps you can assist, what am basically trying to do is Automate Reports Using Reports Services Tool, so i'm trying to get a date Syntax that i will use so that the report can run automatically on monthly basis without my intervention.