Text version of date to actual date

Database

Text version of date to actual date

<html>

I have someone who built a database and put in many tables dates but they used char and the dates are in there like:

41968

41967

41999

 

If I just do a copy paste of a few into Excel and then go from that to Date I get the date like it should be. The above would look like this if I click in Excel Date:

11/25/2014
11/24/2014
12/26/2014

 

If I can figure out how to view them in a select like a date should look then I can fix the tables that look like this with an update query. Any advice besides going back to the person and having them import everything again? It took them several weeks to build this and I want to go back and say run this. I know update queries to fix stuff like this are much faster. I have run into date issues but mine have always been the format you see 11/25/2014 and I need to change it to 2014-11-25.

 </html>


Accepted Solutions
Teradata Employee

Re: Text version of date to actual date

In Excel, 1 corresponds to date'1900-01-01' but for backward compatibility reasons Excel includes 1900-02-29 (which Teradata does not, since 1900 was not a leap year).

 

Try DATE'1899-12-30'+CAST(column AS INTEGER)

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: Text version of date to actual date

In Excel, 1 corresponds to date'1900-01-01' but for backward compatibility reasons Excel includes 1900-02-29 (which Teradata does not, since 1900 was not a leap year).

 

Try DATE'1899-12-30'+CAST(column AS INTEGER)

Senior Apprentice

Re: Text version of date to actual date

Hi,

 

I think the following will fix this for you.

SELECT DATE '1899-12-30' + 41968;

This gives your result from below:

2014-11-25

 

Your other examples come out correctly as well.

 

HTH

Dave

 

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

Re: Text version of date to actual date

You are so smart.

Re: Text version of date to actual date

You are so smart too. They both work perfect. Thanks so much.