Count Days in a date field

Database
Enthusiast

Count Days in a date field

I need to count the total number of days in a date field.  In otherwords,  a date represents a count of one.  My first attempt did not provide the desired result (

EXTRACT(DAY FROM B.last_pmt_txn_dt),1 AS last_purch_day,)  I am new to Teradata and need help with the syntax. 

v/r,

J1eggert

5 REPLIES
Enthusiast

Re: Count Days in a date field

How about 

select count(extract (DAY FROM B.last_pmt_txn_dt))  from tab1.

I am not sure, why you need to extract a day in order to count date.

Cheers,

Raja

Enthusiast

Re: Count Days in a date field

Hi,

Use the follwoing:

select SUM(Extract(DAY FROM COL_NAME))  from TABLENAME;
Khurram
Enthusiast

Re: Count Days in a date field

I have been tasked to do a recency segment.  Number of transactions segment by: 1-14, 15-30, 30-90, etc.  The qry I created (using a CASE WHEN statement) would only give me 1-14, the rest were field with "?".  Does this help to explain the reason for counting days?  If you know if a simpler approach please let me know.

Thank you Raja / M. Saeed Khurram for  your help!

v/r,

J1eggert

Junior Contributor

Re: Count Days in a date field

EXTRACT(DAY) extract the day part of a date, e.g. for 2013-10-26 it's 26.

But i doubt this is what you need, what business question would be based on that?

Could you clarify what you actually want?

The number of days between two dates? datecol1 - datecol2, but why to count it?

The number of distinct dates? COUNT(DISTINCT datecol)

Or just the number of rows with data in it? COUNT(datecol)

Dieter

Enthusiast

Re: Count Days in a date field

Can you explain in more detail what information you are looking for and how does your data looks like?

May be if you share a sample dataset and expected results along with the query that you are working with, only then someone can guide you in write direction!