'INSERT' in Embedded SQL (Base)

[BillL1]

I have used the following to insert new data into a table:
INSERT INTO “T01”
( “PaymentType”,
“TransDate”,
“Payee”,
“WhatBought”,
“UKAmount”
)
SELECT “T04”.“PaymentType”,
NOW(),
“T04”.“Payee”,
“T04”.“Reference”,
“T04”.“UKAmount”
FROM “T05”, “T04”, “T06”
WHERE “T05”.“Payee” = “T04”.“Payee”
AND “T05”.“Day” = “T04”.“Day”
AND “T06”.“Next Month” = “T05”.“MonthNo”

and the query executes properly.

However, if I change the NOW() to a date made up of concatenated components e.g.:
“T04”.“Day” || ‘/’ || “T05”.“Month” || ‘/’ || “T06”.“NextMonth”
it fails, quoting wrong dataa type.
I then tried a simple fixed date substitution i.e. ‘12/02/24’, which also failed.
How do I enter date parameters?
Is there a function which converts data of type Text to type Date?

[RobertG]

Dates in SQL should be formatted as YYYY-MM-DD. “NextMonth” isn’t a year, is it? “Day” and “Month” might be only one number.
Something like this might work:

"T06"."Year"||'-'||RIGHT(0||"T05"."Month",2)||'-'||RIGHT(0||"T04"."Day",2)

Year should have 4 numbers, month always 2 numbers and day also always 2 numbers.

[Villeroy]

Works with 1-digit month and day as well