Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have a column which is having this type of string data 11%2F13%2F2017%2000%3A00%3A00 and I want to convert it into date format.

Is there any fast way to do that because cast and convert doesn't works.

That data comes from a live database table. I have a fulltext column like this:

&Invoice_Date_Start=11%2F13%2F2017%2000%3A00%3A00&I‌​
nvoice_Date_End=11%2‌​F19%2F2017%2000%3A00‌​%3A00&
From where I am extracting using various combination of substring and left function a column name invoice start date and getting that data which i have posted earlier. Now I need to convert it to date format.
                Ok, first why you are storing date as string and like this format? second what would be the result for the string you provided? what is the logic? Can you please edit the question give sample data and desired uotputs with explanation.
– Ilyes
                Nov 20, 2017 at 16:50
                As I can understand there is two dates there Invoice_Date_Start and I‌​nvoice_Date_End. Which one you need? or both of them?
– Ilyes
                Nov 20, 2017 at 17:11
It's URL encoded. Use the UrlDecode function from this link and then cast it:
https://www.codeproject.com/Articles/1005508/URL-Decode-in-T-SQL
select cast(dbo.UrlDecode('11%2F13%2F2017%2000%3A00%3A00') as datetime)
[Edit]
If you're restricted from creating the function, and you only need to replace the same few characters every time for a single, consistent field, you can just use REPLACE:
SELECT CAST(REPLACE(REPLACE(REPLACE('11%2F13%2F2017%2000%3A00%3A00', '%2F', '/'), '%20', ' '), '%3A', ':') AS DATETIME)
                HI i am on Live database and i can't create that function there. so any other way to do that without creating function. thanks
– deep
                Nov 20, 2017 at 17:04
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.