need help converting a date format in a Where clause

[skyline01]

My app's global variables are as follows:

TimeFormat:               h:mm:ss TT

DateFormat:                M/D/YYYY

TimestampFormat:   M/D/YYYY h:mm:ss[.fff] TT

I have a data source (an Excel file) with dates like the following:

9/6/2018 3:50:36 PM

I have another data source (a SQL Server table) with dates like the following:

201-09-06

My SQL table is my date dimension table.  So, I want to filter its data to the range of dates from my Excel source.  So, I am doing the following:

Temp:
Load
    Date(Min(ExcelDate), 'YYYY-MM-DD') as MinDate
    ,Date(Max(ExcelDate), 'YYYY-MM-DD') as MaxDate
Resident
    Excel_table
;

Let vDateMin = Peek('MinDate', 0, Temp);
Let vDateMax = Peek('MaxDate', 0, Temp);

Drop Table Temp;

dimension_date:
Load
    SQLDate as ExcelDate
Where
    SQLDate >= $(vDateMin) and SQLDate <= $(vDateMax)
;

SQL
SELECT
    SQLDate
FROM
    SQL_table
;

However, this is returning 0 rows into the dimension_date table.  How can I resolve this?

[sergio0592]

Try to use interpretation function when you load MinDate and MaxDate from your Excel file.

Temp:
Load
    Date(Min(Date#(ExcelDate,'DD/MM/YYYY hh:mm:ss TT')), 'YYYY-MM-DD') as MinDate
    ,Date(Max(Date#(ExcelDate,'DD/MM/YYYY hh:mm:ss TT')), 'YYYY-MM-DD') as MaxDate
Resident
    Excel_table
;

[skyline01 · Accepted solution]

I figured the solution to my problem.  The error is in my dollar-sign expansion.  The solution is:

dimension_date:
Load
    SQLDate as ExcelDate
Where
    SQLDate >= Date#('$(vDateMin)', 'YYYY-MM-DD') and SQLDate <= Date#('$(vDateMax)', 'YYYY-MM-DD')
;