-1

In reading this,

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

http://www.sqlusa.com/bestpractices/datetimeconversion/

I cannot locate the answer.

How do I convert varchar '060118' (MMDDYY) to date?

  • Check this out: stackoverflow.com/questions/32307690/… – Bert Wagner Feb 27 at 1:26
  • What year is 18? 2018 or 1918? Perhaps obvious, but you want to define that so you know you get the result you're looking for. In SQL server, there's a cut-off config (sp_configure) for what years that belong to 19xx and what belongs to 20xx. – Tibor Karaszi Feb 27 at 7:43
0

There is no method to directly convert this value because SQL implicitly assumes the first two characters are the year when directly converting to DATE and there is no option for providing a custom format mask.

You need to manipulate the string value first to produce a string that you can then convert to a datetime value.

DECLARE @Val VARCHAR(25) = '060118'

SELECT CONVERT(DATETIME, SUBSTRING(@Val, 5, 2) + SUBSTRING(@Val, 1, 2) + SUBSTRING(@Val, 3, 2) , 101)

Result:
06/01/2018

CAST and CONVERT

SUBSTRING

Your Answer

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