SQL Syntax for between dates

Question

I have an orderDate record and I'm trying to get the records from between two dates at a specific time in Query Activity within SFMC:

• Start Date: Friday 6th October 07:00am
• Finish Date: Sunday 22nd November 23:59pm

I've tried using this format YYYY-MM-DDThh:mm:ss+hh:mm 2019-10-06T00:07:00Z but I get an incorrect syntax error near T

I also tried using only the date 2019-10-06 but that doesn't work either, I get 0 records.

It only works this way but ideally, I need to set the dates and time

WHERE convert(date, [OrderDate]) >= convert(date, (getutcdate() - 36))
    AND convert(date, [OrderDate]) <= convert(date, (getutcdate() - 29))

Answer 1

Try WHERE [OrderDate] > DATEADD(hh, 7, '6 October 2019') AND [OrderDate] < '23 November 2019'