SQL - how to convert date that is nvarchar into datetime type and another format

This is unbelievable! I was afraid that 10 wins in a row is kinda not real and might make the players believe they are undefeatable. This had to stop some time , but the cup's final? After we lead 22 points??? ouch, it hurts...

So I cannot sleep tonight, so I desided to bring out something positive out of this "bizayon" - I cannot find another word.

Today I had an interesting task : there was a table where one field was a date but saved as a nvarchar, and was american date style MM/dd/yy h:/mm:ss PM/AM which I needed to insert into another field but as datetime and in an appropriate for Israel 113 format - dd/MM/yyyy hh:mm:ss.

 tbl_SomeTable
|---------------------------|-----------------|
|    nvarchar_timefield     |  DateTimeField  |
|---------------------------|-----------------|
|  "11/26/2005 5:45:30 PM"  |       ?         |
|---------------------------|-----------------|

My first thought was - ah , this is going to be hard , with dateparts and things like that. Thank god, I first tried something easier that I thought might work. Laziness is the engine of progress - that's what my grandfather always said, 'cause a hardworking person would never come up with a washing mashine! So take a look, isn't it nice?

UPDATE    tbl_SomeTable
SET       DateTimeField = Convert(datetime,Convert(datetime, nvarchar_timefield ),113)
Where nvarchar_timefield is not null

What basically happens here is :  convert nvarchar_timefield to datetime type and than turn it into format 113. That's it! Hope it helps someone.

And congrats to hapoel and their fans, well, you deserve it.