Table column date format vs. String
5 views (last 30 days)
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!
Dave Behera on 25 Mar 2016
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).