Convert varchar MMDDYYYY to the date and time MM / DD / YYYY and select the latest date

Question

Ok, so I have a strange problem ... the dates in the table were entered as string values MMDDYYYY, and I'm trying to display in the report MM/DD/YYYYin the report and select only the most recent date related to the ID, because some ID can have multiple dates.

An example of my table:

  ID  |  MyDate  |
------+----------+
  1   | 01302014 |
  1   | 04222014 |
  2   | 01302014 |

What I want to see when I select and paste the tempo into the table is:

  ID  |   MyDate  |
------+-----------+
  1   | 4/22/2014 |
  2   | 1/30/2014 |

I know that storing dates as string values ​​is bad practice, especially when storing them as MMDDYYYY, but does anyone have a solution to this nightmare?

EDIT

, NULL. , , , , Right, Left, Convert.

Answer 1

, , , - .

CONVERT DATE ROW_NUMBER .

DECLARE @tbl TABLE(Id INT, myDate VARCHAR(8))

INSERT @tbl
SELECT 1  ,  '01302014' UNION ALL
SELECT 1  ,  '04222014' UNION ALL
SELECT 2  ,  '01302014'

Query

;WITH C AS(
    SELECT  ROW_NUMBER() OVER (PARTITION BY Id ORDER BY CONVERT(DATETIME, (SUBSTRING(myDate, 5, 4) + '.' + SUBSTRING(myDate, 1, 2) + '.' + SUBSTRING(myDate, 3, 2)), 101) DESC) AS Rn
            ,Id
            ,CAST(CONVERT(DATETIME, (SUBSTRING(myDate, 5, 4) + '.' + SUBSTRING(myDate, 1, 2) + '.' + SUBSTRING(myDate, 3, 2)), 101) AS DATE) AS myDate
    FROM @tbl
)
SELECT Id, myDate
FROM C
WHERE Rn = 1

SQLFiddle Demo

Answer 2

Select id,convert(varchar(11),cast(dateValue as Date),101)
From
(
Select id,MAX(cast(MyDate As Date)) as dateValue
From tableName
Group By id
) t

Answer 3

:

convert(date,SUBSTRING (MyDate,1,2)+'/'+SUBSTRING (MyDate,3,2)+'/'+SUBSTRING (MyDate,5,4),101)

Answer 4

Select CONVERT(datetime,RIGHT('01302014',4) +
                       LEFT('01302014',2) +
                       SUBSTRING('01302014',3,2))

'2014-01-30 00:00:00.000'

;with Cte as (Select Id,CONVERT(datetime,RIGHT(MyDate,4)+LEFT(MyDate,2)+SUBSTRING(MyDate,3,2)) as sDate, C=ROW_NUMBER()
over(PARTITION By Id Order by MyDate desc)
From #Temp)

select * 
from Cte
where C=1

Answer 5

datetime, , :

select id, convert(varchar, max(convert(datetime,right(mydate, 4)+left(mydate,4))), 101)
from #t
group by id

, , datetime , , .

Answer 6

CONVERT, , SQL Server / .

DECLARE @OldDate varchar(8); 
SELECT @OldDate = '04252012';

SELECT CONVERT(datetime, substring(@OldDate,5,4) + '-' + substring(@OldDate,1,2) + '-' + substring(@OldDate,3,2) + 'T00:00:00')

Answer 7

mySQL str_to_date().

STR_TO_DATE(`MyDate`, '%m%d%Y')

DATETIME,

DATE_FORMAT(STR_TO_DATE(`MyDate`, '%m%d%Y'), '%c/%e/%Y')

Answer 8

:

Select ID, Case When IsDate(RIGHT(MyDate,4)+LEFT(MyDate,2)+SUBSTRING(MyDate,3,2)) = 1 
THEN Convert(varchar(10), Convert(datetime, RIGHT(MyDate,4)+LEFT(MyDate,2)+SUBSTRING(MyDate,3,2)), 101) 
        ELSE Null END AS MyDate FROM YourTable a where Case When IsDate(RIGHT(MyDate,4)+LEFT(MyDate,2)+SUBSTRING(MyDate,3,2)) = 1 
        THEN Convert(varchar(10), Convert(datetime,  RIGHT(MyDate,4)+LEFT(MyDate,2)+SUBSTRING(MyDate,3,2)), 101) 
        ELSE Null END = (Select Max(Case When IsDate(RIGHT(MyDate,4)+LEFT(MyDate,2)+SUBSTRING(MyDate,3,2)) = 1 
        THEN Convert(varchar(10), Convert(datetime, RIGHT(MyDate,4)+LEFT(MyDate,2)+SUBSTRING(MyDate,3,2)), 101) 
        ELSE Null END)
        FROM YourTable b
        Where a.ID = b.ID
        )

Answer 9

.

First you will need to convert MyDate to String. Data, for example, (01302014), is not considered as a string, and the leading zero will be deleted when it is converted. So use CAST to create MyDate as a String and add a leading 0 to it. Then you will find the correct 8 characters to get rid of the leading zero in the months from October to December.

Here is the code that should work for you:

CONVERT(Date, SUBSTRING(RIGHT('0' + CAST(MyDate AS VARCHAR(10)), 8), 1, 2) + '/' + SUBSTRING(RIGHT('0' + CAST(MyDate AS VARCHAR(10)), 8), 3, 2) + '/' + SUBSTRING(RIGHT('0' + CAST(MyDate AS VARCHAR(10)), 8), 5, 4), 101)

Hope this helps.