Convert text field to date time in a new view

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

1 Like

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.

1 Like