DAYS BETWEEN 2 DATE FIELDS

Database
Enthusiast

DAYS BETWEEN 2 DATE FIELDS

here are my 2 data fields:

 

,MAX(Applied_Date_of_Switch) 
,MAX(Effective_Date_of_Switch)

 

I simply want to subtract both, so I can get the number of days between the 2 fields for all 300 rows in a Select statement.

 

What is the syntax?

 

 

 

 

5 REPLIES
Junior Contributor

Re: DAYS BETWEEN 2 DATE FIELDS

MAX(Applied_Date_of_Switch)  - MAX(Effective_Date_of_Switch)

returns the number of days between both dates.

 

Enthusiast

Re: DAYS BETWEEN 2 DATE FIELDS

Yes I had that earlier.

 

However, my result is, for example, displaying as as 951603.0000 when it should be just displaying as 1 day.

 

what can i do??

 

 

Junior Contributor

Re: DAYS BETWEEN 2 DATE FIELDS

What's the datatype of those columns? Doesn't seem to be a DATE.
Enthusiast

Re: DAYS BETWEEN 2 DATE FIELDS

yes they are DATE types

 

still getting that strange answer, which is not the number of days between the dates

 

seems straightforward but driving me crazy...any more ideas???

Teradata Employee

Re: DAYS BETWEEN 2 DATE FIELDS

When I "Select date'2016-11-14' - date'2016-11-13'," I get 1. What are your dates?!?

You could try: cast(MAX(Applied_Date_of_Switch) as date) - cast(MAX(Effective_Date_of_Switch) as date)