Question: I am using SQL Server and in my database I have a date column of type `varchar` and has data like `04042011`. I am trying to write a query that is being used for another program and it is requesting the format in a military format such as `YYYY-MM-DD`. Is there any way to do this on the fly for `HireDate` and `SeparationDate`?

```
SELECT [EmployeeTC_No] AS "Employee TC #"
      ,[pye_nlast] AS "Name Last"
      ,[pye_nfirst] AS "Name First"
      ,[Dept] AS "Department"
      ,CASE WHEN [pye_status] = 'A' THEN 1 
       WHEN [pye_status] = 'T' THEN 0 
       ELSE NULL END AS "Active"
      ,[HireDate] AS "Hire Date"
      ,[SeparationDate] AS "Separation Date"
  FROM [OnBaseWorking].[dbo].[Employees]
  WHERE EmployeeTC_No != 'Not Assigned'
    AND LOWER(EmployeeTC_No) = LOWER('@primary')

```

Any help would be greatly appreciated. I only have read rights. So I am hoping I can do this on the fly I know dates should not be data type `varchar` and should be data type date. But I have no control over this and just trying to do what I can.

Answer: 
If truly 8 characters. I would stress use try\_convert() in case you have some bogus data.

```
Select try_convert(date,right(HierDate,4)+left(HierDate,4))
 From  YourTable

```

To Trap EMPTY values. This will return a NULL and not 1900-01-01

```
Select try_convert(date,nullif(HierDate,'')+left(HierDate,4))
 From  YourTable

```
Concatenate the string from the [substrings](https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-2017) that you want.

```
SUBSTRING(YourField, {The position that the year starts}, 4)
+'-'+
SUBSTRING(YourField, {The position that the month starts}, 2)
+'-'+
SUBSTRING(YourField, {The position that the day starts}, 2)

```