Click here to Skip to main content
15,797,984 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a data like 'Sat, February 20, 2016' in columns of my SQL Table and wanted to convert it to date or datetime value in a query.

I need it as date or datetime to use in the comparative query.

Any help/guide is required.

What I have tried:

After searching google for a bit of time, I am still unable to convert or cast it to my required format.
Updated 28-Jul-16 0:02am
RossMW 28-Jul-16 4:41am    
How consistent is the data format. If it's consistent you can look at breaking it down to its respective parts, and put it back in the format you want to convert it to a date field.
But if it's a human input text field, I don't fancy your chances unless you planning to data scrub.
VICK 28-Jul-16 5:27am    
Its a human input fields. :(

Pretty much, you can't - or at least, not simply.
The problem is that SQL doesn't have a date format style that includes the day of the week: SQL Server CONVERT() Function[^] so you need to do some farily serious string manipulations first.
If you strip off the day of the week, it's fairly simple:
CONVERT(DATE, SUBSTRING('Sat, February 20, 2016', 6 , 999), 109)
Will do it.
But...having to do this is normally a sign that your DB design is badly flawed: you should not be storing dates in string format at all because you always get problems like this. I'd strongly suggest that the real solution is to write a quick app that converts your whole DB to use a DATETIME column instead of NVARCHAR - it'll save you a lot of grief in the future.
Share this answer
VICK 28-Jul-16 5:26am    
Agreed about the "Badly flawed DB Design", but its not possible for me to change the DB Schema, Rather using your suggested answer. It works. :)
Suvendu Shekhar Giri 28-Jul-16 5:38am    
Perfect! 5ed :)
You can do something like following-

SET @MyVar='Sat, February 20, 2016'


Hope, it helps :)
Share this answer
VICK 28-Jul-16 5:35am    
It works, but preferred Griff's answer posted above..
Thanks for your reply. My 5+.
Suvendu Shekhar Giri 28-Jul-16 5:38am    
True, That's the better way to do it.

Thanks for upvote :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900