Month difference

Database
Fan

Month difference

Hi,

Can anybody please help me, how to get month difference between current date and a date column. Say for example i have a Table called LogonInfo in that a column called LastLogonTime now i want to get the info like users who has not logged in since 6 months.

Thanks.


Accepted Solutions
Apprentice

Re: Month difference

Hi Ruban,

 

As per the Teradata SQL Functions manual, "The ADD_MONTHS function provides for adding or subtracting months or years, handling the variable number of days involved."

 

So what the following code does is:

WHERE ADD_MONTHS(DATE,-3) > lastlogontime

It starts with the current date (TD built-in function DATE = current date)

Subtracts 3 months from it (-3)

Returns a date value.

 

Assume that you run this today, the return from the ADD_MONTHS function will be 6th June 2017 (3 months ago).

If you changed the -3 to -6 it will return 6th March 2017.

If you changed the -3 to 2 it will return 6th November 2017.

 

In your original example "list users who have not logged on for 6 months" my suggested code is:

WHERE ADD_MONTHS(DATE,-6) > lastlogontime

The ADD_MONTHS function returns the date 6 months ago from the date you run the sql (6th March 2017 for today).

This is compared to logondatetime.

The only rows that meet that test are those where the logondatetime is <= 6th March 2017 - i.e. users who have not logged on in the last 6 months.

 

Does that help?

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
6 REPLIES
Apprentice

Re: Month difference

Hi,

Use the ADD_MONTHS function.

WHERE ADD_MONTHS(DATE,-6) > lastlogontime

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

Re: Month difference

Hi Dave,

 

Thanks for your input it works for the below value only however if I change the integer value to anything more than 3 then the query does not return any record.

WHERE ADD_MONTHS(DATE,-3) > lastlogontime

Can you help me to understand what exactly this ADD_MONTHS function calculating.

 

Thanks,

Ruban

Apprentice

Re: Month difference

Hi Ruban,

 

As per the Teradata SQL Functions manual, "The ADD_MONTHS function provides for adding or subtracting months or years, handling the variable number of days involved."

 

So what the following code does is:

WHERE ADD_MONTHS(DATE,-3) > lastlogontime

It starts with the current date (TD built-in function DATE = current date)

Subtracts 3 months from it (-3)

Returns a date value.

 

Assume that you run this today, the return from the ADD_MONTHS function will be 6th June 2017 (3 months ago).

If you changed the -3 to -6 it will return 6th March 2017.

If you changed the -3 to 2 it will return 6th November 2017.

 

In your original example "list users who have not logged on for 6 months" my suggested code is:

WHERE ADD_MONTHS(DATE,-6) > lastlogontime

The ADD_MONTHS function returns the date 6 months ago from the date you run the sql (6th March 2017 for today).

This is compared to logondatetime.

The only rows that meet that test are those where the logondatetime is <= 6th March 2017 - i.e. users who have not logged on in the last 6 months.

 

Does that help?

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

Re: Month difference

Hi Dave,

 

Excellent explaination thank you so much now i understood.

 

Thanks,

Ruban

Teradata Employee

Re: Month difference

Note there is also an OADD_MONTHS() function, which is exactly the same as ADD_MONTHS except that if the starting date is the last day of the month, then the result date is also the last day of the month.

 

Add_Months(date'2017-02-28',1) = date'2017-03-28'

OAdd_Months(date'2017-02-28',1) = date'2017-03-31'

Fan

Re: Month difference

Dear Coleman,

 

Thanks it was helpful indeed.