Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a simple select statment with a where clause that contains date. I would like to replace the Date column reference with a DATE datatype parameter. I have searched this forum numerous times and I can only find examples where the parameter is a TEXT datatype. If that's the only way that it can be done, I will accept that. How do I formate the DATA Parameter reference in my direct query SQL statment? '"StartTime"' is how my date parm is currently formatted and that throws a Token Comma expected error. Help!
Solved! Go to Solution.
The answer turned out to be making every column be the same datatype -- in this case a DATE. The syntax that finally worked was this:
CAST(CreatedDate as DATE) >= '"&StartTime&"'
The answer turned out to be making every column be the same datatype -- in this case a DATE. The syntax that finally worked was this:
CAST(CreatedDate as DATE) >= '"&StartTime&"'
let
Source = Sql.Database("hadev19", "ECCOVIA", [Query="WITH AllRows AS (#(lf)select#(lf) LedgerType,#(lf) FamilyAcct,#(lf) ClientID,#(lf) CreatedDate,#(lf) Charge_Amount,#(lf) [Service],#(lf) PaymentsToDate,#(lf) Balance#(lf) #(lf) from ECCOVIA.dbo.[Account_LedgerByFamily] #(lf)where LedgerType in ('Charge', 'Payment') #(lf)and CAST(CreatedDate as DATE) >= '"StartTime"' #(lf)--group by LedgerType, FamilyAcct, ClientID#(lf))#(lf) #(lf)select#(lf) #(lf) LedgerType,#(lf) FamilyAcct,#(lf) ClientID, #(lf) MAX(CAST(CreatedDate as DATE)),#(lf) Charge_Amount,#(lf) [Service],#(lf) PaymentsToDate,#(lf) Balance#(lf) #(lf)from#(lf)#(lf)AllRows#(lf)where NOT Exists (select * from ECCOVIA.dbo.[Account_LedgerByFamily] b#(lf)where b.ClientID = AllRows.ClientID#(lf)And LedgerType = 'Payment'#(lf)and CreatedDate >= '"StartTime"'#(lf)group by LedgerType, FamilyAcct, ClientID,Charge_Amount,[Service], PaymentsToDate, Balance#(lf);"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"", "CreatedDate"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"CreatedDate", type date}, {"FamilyAcct", type text}, {"ClientID", type text}})
in
#"Changed Type"
My Query is above.
@FarmerKenny Write your code like this:
let
Source = Sql.Database (
"autumn\sql2019",
"ContosoRetailDW",
[
Query =
"
SELECT *
FROM Dates
WHERE [Date] =
"
& "'"
& DateTime.ToText ( TargetDateParameter )
& "'"
]
)
in
Source