Convert varchar to date format

Aleks
Aleks used Ask the Experts™
on
I have a field which is currently varchar(42) and holds data in this format:  2016-09-30 @ 1:33 PM

I need to remove the space before the @ and anything after that space, including the @ so that only the date remains.
Then if possible use a script to conver the: yyyy/mm/dd into a date format

I am using MS SQL 2012
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Try this:
convert(date, substring(some_column,1,charindex(' ',some_column)),102)

Author

Commented:
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'convert'.

I want to remove the text after @ including the space before it, that would be more helpful first.

Author

Commented:
Also, the script above doesn't indicate which table.
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Of course you need to write something like
select convert(date, substring(some_column,1,charindex(' ',some_column)),102) from some_table

Open in new window

to integrate it into your current select, using your real column and table name.

Author

Commented:
Thanks to both

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial