topic Re: need help converting a date format in a Where clause in App Development

need help converting a date format in a Where clause

skyline01 — Tue, 21 Dec 2021 21:30:18 GMT

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?

Re: need help converting a date format in a Where clause

sergio0592 — Thu, 28 Feb 2019 20:34:05 GMT

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
;

Re: need help converting a date format in a Where clause

skyline01 — Thu, 28 Feb 2019 20:40:47 GMT

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')
;