Table column date format vs. String

Hi,

I have a table A with column 'dates' and the format is string like '04/01/2016'. I have another table B with column 'dates' and the format is '4/1/2016'. Now I want to do a join by the 'dates' column, but the do not match, and I get 0 rows. How to make the two columns match? Thanks a lot!

Jennifer

Answer 1

[Dave Behera]

Are you using database tables? In that case you can try creating a table with 2 columns where each row corresponds to the same dates in the other two tables. Then, you can do a join on the three of them to get the final result.

So, if your original tables are A (with date format '04/01/2016') and B (with date format '4/1/2016'), you can create a table C with two columns. A row in C will be like

'04/01/2016' | '4/1/2016'

Then you do

D = (A JOIN C on C.col1)

O = D JOIN B on D.col2

O should give you the output, but remember that the columns from C will still be there in O. You can remove them using the SELECT statement.

To create the table C, you will have to take all dates from table A and parse them to generate new strings (similar to dates in B).