Experiencing Challenges with TimeStamp in Teradata

Database

Experiencing Challenges with TimeStamp in Teradata

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)

FROM

PROD_BI.BI_IT_TAXPAYER_H_KEY

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

AND

2. SELECT COUNT(1)

FROM PROD_BI.BI_IT_TAXPAYER_H_KEY

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

3 REPLIES
Junior Contributor

Re: Experiencing Challenges with TimeStamp in Teradata

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.

Dieter

Re: Experiencing Challenges with TimeStamp in Teradata

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.

Re: Experiencing Challenges with TimeStamp in Teradata

meant to say " i see what you mean"