CODEWITHSUNDEEP
sql-servert-sqldatetype-conversion
samy
samy

Reputation: 37

String conversion to valid date

I need to convert a string to a valid date format

I have tried using cast and convert functions to no avail.

Convert( mystring, mm/dd/yy)

I have got a string like: Tuesday, July 09, 2019 12:00 AM to get the output mm/dd/yy . I am not able to get this work.

Tuesday, July 09, 2019 12:00 AM---> mm/dd/yy

Upvotes: 2

Views: 119

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

The most important hint was provided by Larnu as a comment: Do not store date as string, rather use the native date formats and think about the string format in output scenarios only.

So hopefully you are trying to fix this technical issue and therefore you need this conversion in order to store the values in neatly typed columns ;-)

The suggestion by gotqn is the best, as you obviously need to use a specific language/culture and TRY_PARSE is the only approach which allows to specify this parameter. TRY_PARSE will need at least v2012 and - this might be a draw back, it is really slow.

So, if this is a one-way-ticket (just one conversion in order to store your values correctly) and you have a very large count of rows, you might go this route:

DECLARE @yourstring VARCHAR(100)='Tuesday, July 09, 2019 12:00 AM';

SET LANGUAGE ENGLISH;
SELECT CAST(SUBSTRING(@yourstring,CHARINDEX(',',@yourstring)+1,100) AS datetime);

The simple idea is: Cut away the leading Weekday's name. The rest is working implitly.

Upvotes: 2

gotqn
gotqn

Reputation: 43636

Try this:

SELECT TRY_PARSE('Tuesday, July 09, 2019 12:00 AM' AS DATETIME USING 'en-US')

to convert to valid date.

Then:

SELECT CONVERT(VARCHAR(10), TRY_PARSE('Tuesday, July 09, 2019 12:00 AM' AS DATETIME USING 'en-US'), 101)

to convert it to mm/dd/yy.

Upvotes: 3

Related Questions