I have a table in which a column is nvarchar(Max) and date value is stored as for eg: 'Tue Aug 22 00:00:00 PDT 2017'

How do I convert this value to proper datetime value

I tried Try_Convert, Try_parse,Parse but all fail to convert it to datetime

Any help or suggestion

  • When you say it fails, do you mean you get an error, or that it doesn't convert properly? – Erik Darling Sep 8 '17 at 15:16
  • I get error as the column because the column also contains other text values so I was filter only the values which was date but their conversion to actual date failed – Ranjeet Sep 8 '17 at 15:54
  • @Ranjeet - Please edit this question to add this info; I'd also suggest adding some example values of the actual contents of the NVARCHAR(MAX) value, and of the results from your filtering. – RDFozz Sep 8 '17 at 16:26

You likely need to perform some string manipulation to get it to convert properly. If all of your strings are fixed width and follow the same pattern as provided in your example, the following is a possible way to get the conversion to work:

DECLARE @myString NVARCHAR(50) = N'Tue Aug 22 00:00:00 PDT 2017'

--                       Month and Day              + Year                + HH:MM:SS using I assume 24 hour clock
SELECT CONVERT(DATETIME, SUBSTRING(@myString, 5, 7) + RIGHT(@myString, 4) + SUBSTRING(@myString, 11, 10))

If your data shows any pattern variances, please append some additional examples to your question.

| improve this answer | |
  • I tried using the below with success TRY_PARSE(REPLACE(REPLACE(columname,'PDT ',''),'00:00:00 ','') AS DATE) – Ranjeet Sep 8 '17 at 15:51
  • There's a number of different approaches you can use, so I'm glad you found something that works. :) – John Eisbrener Sep 8 '17 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.