Return new date string in the same format as input-CodePudding

I've got two columns: varchar column InputValue with input dates (all strings pass IsDate(InputValue)=1 validation) and datetime column NewDate with new dates. How do I return new dates as a string in the same format as in input dates? Example (I need OutputValue to contain NewDate value and be formatted the same way as InputValue):

    InputValue    NewDate       OutputValue
    (varchar)     (datetime)    (varchar)
    20010101      02/02/2002    20020202
    01-01-2000    12/25/2001    12-25-2001
    Aug 12 2012   11/15/2000    Nov 15 2000

Is there any way to find out the style of the input date, so I can just use CONVERT(varchar, NewDate, @inputStyle)? Or maybe you have any custom code to get as close to the result as possible? Thank you!

CodePudding user response：

Well, it would be quite an undertaking to write rules for all possible date formats, but if you just wanted those and for reasons that are your own entirely, it could be done with iif functions:

  DECLARE @T TABLE (InputValue varchar(20), NewDate date, OutputValue varchar(20))
  
  INSERT INTO @T SELECT '20010101', '02/02/2002', '20020202'
  INSERT INTO @T SELECT '01-01-2000', '12/25/2001', '12-25-2001'
  INSERT INTO @T SELECT 'Aug 12 2012', '11/15/2000', 'Nov 15 2000'

  select    InputValue
          , NewDate
          , OutputValue
          , iif(len(InputValue) = 8, convert(varchar(20), NewDate, 112)
            , iif(InputValue LIKE '[A-Za-z]%', convert(varchar(20), NewDate, 107)
                , convert(varchar(20), NewDate, 32))) derived
  from @t

CodePudding user response：

You can use TRY_CONVERT to attempt a conversion using a variety of styles, then convert the value back and compare it to see if the style matches. See also the documentation.

DECLARE @T TABLE (InputValue varchar(30), NewDate date, ExpectedValue varchar(30));

INSERT INTO @T VALUES
('20010101', '02/02/2002', '20020202'),
('01-01-2000', '12/25/2001', '12-25-2001'),
('Aug 12 2012', '11/15/2000', 'Nov 15 2000');

SELECT *,
  OutputValue = (
    SELECT TOP (1)
      OutputValue = CONVERT(varchar(30), t.NewDate, v.style)
    FROM (VALUES
        (0),(110),(112)
    ) v(style)
    WHERE t.InputValue = CONVERT(varchar(30), TRY_CONVERT(date, t.InputValue, v.style), v.style)
  )
FROM @T t;

I've only added three styles. You can add all of them if you think they may be relevant

    FROM (VALUES
        (0),(1),(2),(3),(4),(5),(6),(7),(8),(24),(10),(11),(12),(13),(14),(20),(21),(22),(100),(101),(102),(103),(104),(105),(106),(107),(108),(9),(110),(111),(112),(113),(114),(120),(25),(121),(23),(126),(127),(130),(131)
    ) v(style)

It goes without saying that this type of table design is really bad.