Setting TIMESTAMP in BTEQ SCRITPS 1

[DFW1999]

Hi,

I have quite a few bteq scripts which I am executing from Perl program. These scripts I need to run during the first 10 days of every month( on the one of the first 10 days of each month). The dates are in the following format for the previous month:

"
where START_TS
BETWEEN cast (CURRENT_TIMESTAMP'2004-05-01 00:00:00.00000' as timestamp) AND cast ('2004-05-30 23:23:59.99999' as timestamp) "

In the above sql I am hard coding both dates. Here I want to automate these scripts so I do not have change starting and ending timestamp each month.

Any help would be appreciated.
thanks

 

[dnoeth]

where
START_TS >= add_months(current_date - extract(day from current_date) + 1, -1)
AND START_TS < current_date - extract(day from current_date) + 1


current_date - extract(day from current_date)
returns the last day of the previous month

current_date - extract(day from current_date) + 1
returns the first day of the current month

add_months("first day of current month", -1)
returns the first day of the previous month

When a date is compared to a Timestamp it's converted to
yyyy-mm-dd 00:00:00.000000


So above will be
where
START_TS >= '2004-05-01 00:00:00.000000'
AND START_TS < '2004-06-01 00:00:00.000000'

Dieter

 

[DFW1999]

Hello Dnoeth,

I appreciate your detailed reply.

I see your following comment:
"
When a date is compared to a Timestamp it's converted to
yyyy-mm-dd 00:00:00.000000
"
One thing I want to re-confirm with you. In the issue which I stated to you, based on the logic I need to fetch all the records which have timestamp range
FROM
'2004-05-01 00:00:00.00000'
TO
'2004-05-30 23:23:59.99999'

As you see the time is in timestamp format.

Does the solution which you recommended take care of these conditions?


I do not want to loose even a single reocrd based on the timestamp format.

Thanks

 

[dnoeth]

START_TS >= '2004-05-01 00:00:00.000000'
AND START_TS < '2004-06-01 00:00:00.000000'

is exactly the same as

Start_TS BETWEEN '2004-05-01 00:00:00.000000'
TO '2004-05-30 23:23:59.999999'


That's why i didn't use BETWEEN, which is
START_TS >= ... AND START_TS <= ...
and which might result in an extra row with a TS of
'2004-06-01 00:00:00.000000' ;-)

Btw, do you really store Timestamps with that precision?
Do you have a real time OS, because Teradata only provides up to 2 digits, e.g.
2004-06-08 16:46:15.450000+00:00

Dieter

 

[DFW1999]

Hello Dnoeth,

I am dealing with call detail record data. Here we are using timestamp to go minute/Seconds levels. This was the reason I asked for more info from you.

Anyway you did a great job in explaining this issue.

Thanks