Q:

SQL Server 2008 - Create From and To Date from Single Date Column

To start the table I'm dealing with has inconsistency as there are blank tenant values something I cannot control (vendor will take time to fix it). Also the date columns are varchar as that how it's in the actual table. Again something I cannot control and for the purpose of the report to pick the max date, the date columns need to changed from char to date. The database is running on SQL Server 2008 so cannot use LEAD function
I have a @rent table variable as below which holds the tenant rent charged values. I.e every time a tenant rent is updated\changed it gets applied in the @rent table. In certain cases the rent can be updated multiple times in a day or changed in the future if teams find incorrect rent was applied (notice the created date_sql). The Date_changed_SQL indicates when the rent is actually applied. DATE_CHANGED_SQL column will be used to create From to To columns. 
In most cases there will be a duplicate DATE_CHANGED_SQL but based on the CREATED_DATE_SQL and time of the latest TOT_DEB_Val needs to be selected
Sample data:
Declare @rent TABLE 
    ([R23_SQL_ID] int, [PROP_CODE] int, [TENANT_NUMB] varchar(5), [TOT_DEB_VAL] int, [DATE_CHANGED_SQL] varchar(10), [TIME_CHANGED_SQL] varchar(5), [CREATED_DATE_SQL] varchar(10), [CREATED_TIME_SQL] varchar(5))

INSERT INTO @rent
    ([R23_SQL_ID], [PROP_CODE], [TENANT_NUMB], [TOT_DEB_VAL], [DATE_CHANGED_SQL], [TIME_CHANGED_SQL], [CREATED_DATE_SQL], [CREATED_TIME_SQL])
VALUES
    (12080, 2524, '41673', 77.49, '2011-07-28', '04:42', '2013-03-19', '04:42'),
    (12081, 2524, '41673', 79.42, '2012-02-02', '04:42', '2013-03-19', '04:42'),
    (12082, 2524, '41673', 79.95, '2012-08-16', '04:42', '2013-03-19', '04:42'),
    (21819, 2524, '', 67.91, '2015-01-15', '09:39', '2015-01-15', '09:39'),
    (21820, 2524, '51500', 67.91, '2015-01-16', '', '2015-01-15', '09:45'),
    (31729, 2524, '51500', 67.91, '2016-08-08', '', '2016-08-08', ''),
    (31152, 2524, '51500', 193.48, '2016-09-05', '10:53', '2016-09-05', '10:53'),
    (34215, 2524, '', 89.86, '2017-03-14', '18:53', '2017-03-14', '18:53'),
    (34216, 2524, '53407', 89.86, '2017-03-15', '', '2017-03-14', '18:53'),
    (34260, 2524, '53407', 89.86, '2017-03-15', '12:32', '2017-03-20', '12:32'),
     (29461, 1589, '33659', 151.78, '2000-08-21', '19:00', '2016-08-16', '19:00'),
    (8356, 1589, '33659', 136.84, '2011-05-26', '04:42', '2013-03-19', '04:42'),
    (8357, 1589, '33659', 144.36, '2011-11-24', '04:42', '2013-03-19', '04:42'),
    (8358, 1589, '33659', 147.91, '2012-05-31', '04:42', '2013-03-19', '04:42'),
    (8359, 1589, '33659', 151.78, '2012-11-29', '04:42', '2013-03-19', '04:42'),
    (14502, 1589, '33659', 200.00, '2013-08-12', '08:30', '2013-08-12', '08:30'),
    (15476, 1589, '', 157.68, '2013-11-22', '09:49', '2013-11-22', '09:49'),
    (17846, 1589, '50602', 157.68, '2013-11-29', '14:00', '2014-05-21', '14:00'),
    (18548, 1589, '50980', 157.68, '2014-06-18', '', '2014-06-18', '13:21'),
    (18547, 1589, '50980', 160.69, '2014-06-18', '13:21', '2014-06-18', '13:21'),
    (20351, 1589, '51343', 160.69, '2014-11-07', '', '2014-11-05', '14:20'),
    (24096, 1589, '51343', 163.74, '2015-07-27', '07:34', '2015-07-27', '07:34'),
    (26286, 1589, '51343', 165.01, '2016-01-25', '08:59', '2016-01-25', '08:59'),
    (28168, 1589, '51343', 166.31, '2016-06-13', '11:02', '2016-06-13', '11:02'),
    (32751, 1589, '51343', 166.80, '2016-12-12', '08:52', '2016-12-12', '08:52'),
    (34058, 1589, '53386', 110.91, '2017-03-07', '', '2017-03-07', '08:22'),
    (34057, 1589, '53386', 110.91, '2017-03-07', '08:17', '2017-03-07', '08:17'),
    (34189, 1589, '53386', 110.91, '2017-03-07', '13:19', '2017-03-13', '13:19')

Requirement: I'm trying to create a view to show the rent period (From and To), the rent value per property and tenant as below
Desired Result Screenshot

A:

The following seems to be pretty close to what you want. You may need to adjust it a little. It doesn't quite give you the results as per your data set ... but it's pretty close and should give you something to work with.
/*cte assigns a row number to each entry, so we can join to the following row for that property*/
with cte as (
select 
    R23_SQL_ID,
    PROP_CODE,
    TENANT_NUMB,
    DATE_CHANGED_SQL,
    TOT_DEB_VAL,
    CREATED_DATE_SQL,
    ROW_NUMBER() OVER (PARTITION BY PROP_CODE ORDER BY DATE_CHANGED_SQL, TIME_CHANGED_SQL) as Seqn
from @rent
    --I wasn't sure about this where clause, you may want to get rid of it, but the results were closer to what you asked for with it in.
    where TENANT_NUMB <> ''
   )

select
    rentStart.R23_SQL_ID,
    rentStart.PROP_CODE,
    rentStart.TENANT_NUMB,
    rentStart.DATE_CHANGED_SQL as From_Date,
    --where you've got to the last row, I've put in "today" as the end date
    convert(char(10),isnull(dateadd(dd,-1,rentEnd.DATE_CHANGED_SQL), getdate()),121) as To_Date,
    rentStart.TOT_DEB_VAL,
    rentStart.CREATED_DATE_SQL
from
    cte as rentStart
    LEFT JOIN cte as rentEnd ON
        rentStart.Prop_Code = rentEnd .Prop_Code AND
        rentStart.Seqn + 1 = rentEnd .Seqn
where
    --Filtering to just the property you were showing
    rentStart.Prop_Code = 2524
    --This is the attempt to look only for the final change in the day. It may be better handled in the CTE rather than here.
AND (rentStart.DATE_CHANGED_SQL < rentEnd.Date_CHANGED_SQL OR rentEnd.DATE_CHANGED_SQL is null)

