how to do a date diff between two date that are formatted as varchar?

Database
Enthusiast

how to do a date diff between two date that are formatted as varchar?

Hi, I have a table T in my teradata database that has claims for all my customers. below is a picture on how the data looks like. I need to pull all claims from my table T based on date criteria.i want to pull all records where Date 2 is either equal to Date1 or falls within 30 days from Date 1. both Date1 and Date 2 are formatted as varchar YYYY-MM-DD. i know the fnction invloves some cast to do date diff but i can't figure it out. any help is appreciated.

 

Mike

 

Capture.JPG


Accepted Solutions
Teradata Employee

Re: how to do a date diff between two date that are formatted as varchar?

Hi Mike,

 

Try out the following query :

select t2.*
  from Table1 as t1
  join Table2 as t2 on t2.Customer = t1.Customer
 where to_date(t2.Date3, 'yyyy-mm-dd')  = to_date(t1.Date1, 'yyyy-mm-dd')
   and to_date(t2.Date4, 'yyyy-mm-dd') >= to_date(t1.Date2, 'yyyy-mm-dd') + 30;
1 ACCEPTED SOLUTION
6 REPLIES
Highlighted
Teradata Employee

Re: how to do a date diff between two date that are formatted as varchar?

You may have missed it but I have answered you here :

https://community.teradata.com/t5/General/How-to-pull-data-before-and-after-an-event-in-one-table/m-...

 

You have to use the to_date function : to_date(date1, 'yyyy-mm-dd') will convert your date1 varchar to date datatype.

But all records have to follow this format. That's one of the many arguments why it's NOT a good idea to store dates as strings.

Enthusiast

Re: how to do a date diff between two date that are formatted as varchar?

waldar, I just need your help one more time. you've been amazin at getting back to me. Teradata is not my thing but I have to use it. 

 

please see below. my table 1 has unique cutomers and table 2 has all my customers (including the ones from table 1 and more). i like to join both tables on customer and pull all records  from table 2 that meet this criteria: Date3 is equal to Date1 and Date3 higher than Date2 by 30 days. again date in formatted as Varchar 20

 

any help is greatly appreciated. 

 

Mike

 

Capture.JPG

Teradata Employee

Re: how to do a date diff between two date that are formatted as varchar?

Hi Mike,

 

Try out the following query :

select t2.*
  from Table1 as t1
  join Table2 as t2 on t2.Customer = t1.Customer
 where to_date(t2.Date3, 'yyyy-mm-dd')  = to_date(t1.Date1, 'yyyy-mm-dd')
   and to_date(t2.Date4, 'yyyy-mm-dd') >= to_date(t1.Date2, 'yyyy-mm-dd') + 30;
Enthusiast

Re: how to do a date diff between two date that are formatted as varchar?

Waldar, Thanks!

Enthusiast

Re: how to do a date diff between two date that are formatted as varchar?

@Waldar, one last question, sorry things keep changing, I promise this is my last question in this topic. i want same logic but I want Date 3 to be in between Date 1 and Date 2 and Date 3 higher than date 2 by 30 days. Thanks again

Teradata Employee

Re: how to do a date diff between two date that are formatted as varchar?

Hi Mike,

 

No worries, questions on a public forum are a good feature.

 

Try out this code :

select t2.*
  from Table1 as t1
  join Table2 as t2 on t2.Customer = t1.Customer
 where to_date(t2.Date3, 'yyyy-mm-dd') >= to_date(t1.Date1, 'yyyy-mm-dd')
   and to_date(t2.Date3, 'yyyy-mm-dd') <= to_date(t1.Date2, 'yyyy-mm-dd') -- check for need about strict or equal inegality
   and to_date(t2.Date3, 'yyyy-mm-dd') >= to_date(t1.Date2, 'yyyy-mm-dd') + 30;