i have a text field in one table and a date time field in another, and need to join the 2 tables in a new view with the date field. The text field is in the format 20191025, any help is greatly appreciated. I tried convert and cast with no luck, unless my syntax is incorrect. The table and field for each are JC_TRANSACTION_HISTORY_MC.Tran_Date_Text and the other is PR_TIME_CARD_HISTORY_MC.Work_Date.
If the dates are all good, no bad data, you can do this:
CAST(JC_TRANSACTION_HISTORY_MC.Tran_Date_Text AS date) = PR_TIME_CARD_HISTORY_MC.Work_Date
If you do have bad data - you can try:
TRY_CAST(JC_TRANSACTION_HISTORY_MC.Tran_Date_Text AS date) = PR_TIME_CARD_HISTORY_MC.Work_Date)
If you can - I would recommend adding a computed column to the table - persisting the computed column - and create an index on that column. Then - you just reference the computed column in your query.