All Forums Database
j1eggert 36 posts Joined 09/13
23 Oct 2013
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. 

Raja_KT 1246 posts Joined 07/09
23 Oct 2013

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.

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

M.Saeed Khurram 544 posts Joined 09/12
23 Oct 2013

Use the follwoing:

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



j1eggert 36 posts Joined 09/13
24 Oct 2013

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!

dnoeth 4628 posts Joined 11/04
26 Oct 2013

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)


Qaisar Kiani 337 posts Joined 11/05
26 Oct 2013

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!

You must sign in to leave a comment.