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. 
 
v/r,
J1eggert

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.
 
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
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

Hi,
Use the follwoing:

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

 

Khurram

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!
 
v/r,
J1eggert

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)
Dieter

Dieter

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.