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
Highlighted
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 6
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

Highlighted
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;