Convert dd/mm/yyyy in String to Date format using

I am writing an SQL query using SSMS where one column will return a date. However the date may come from a couple of possible sources controlled with the use of a CASE expression.

The first column is of datatype varchar(8) and is successfully converted to a date using

FORMAT(CAST(tlUserField1 as date),'dd/MM/yyyy')

However my second column is varchar(30) and when using the same option I get the following error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

I then tried to use

CONVERT (datetime2, tlUserField1, 103)

which returns the same error (I have also tried date and datetime with the same result)

It should be noted (not sure if this effects the outcome) that the first column is formatted as YYYYMMDD and the second field is DD/MM/YYYY

Any assistance would be greatly appreciated.

If I understand you correct then you have a field that can have a date stored as varchar either like '19/07/2017' or like '20170719'

To convert a varchar field to date (not recommended using right column type is better) you can use the convert function. In the convert function you can add a parameter to tell the Convert function what format to expect for the convert.

More info about this function can be found here https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

Basic it looks like this :

convert(date, datefield, 103)

103 is the code for British/French (format dd/MM/yyyy)

Now look at these examples

declare @date varchar(30) = '20170719'

select case 
  when charindex('/', @date) > 0 then convert(date, @date, 103) 
  else convert(date, @date, 112) 
end as ConvertedDate

second example:

declare @date varchar(30) = '19/07/2017'

select case 
  when charindex('/', @date) > 0 then convert(date, @date, 103) 
  else convert(date, @date, 112) 
end as ConvertedDate

Both will succesfully convert the varchar into a date.
Notice that I enter a different format in the @date field each time, and in the select I first determine what format to use (is there a / in the value or not) and then use the correct value for the format parameter.

This is however not full proof offcourse since you never know what value can be in the varchar field.

EDIT:

The format how the date is shown is not depending on above queries. That depends on settings of your database.
If you want to always show it as dd/MM/yyyy you can use the format function.

Example :

select format(getdate(), 'dd/MM/yyyy')

returns for today:

19/07/2017

in my example it would than be

declare @date varchar(30) = '20170719'

select format( case 
                 when charindex('/', @date) > 0 then convert(date, @date, 103) 
                 else convert(date, @date, 112) 
               end,
               'dd/MM/yyyy') as ConvertedDate