Default to last sunday taking client time zone into account if no datetime is passed in

Question

I have a procedure that has @from and @to date input parameters. The data type for both these types is DateTime. This procedure can be called in from any time zone and the values in the DB are stored in UTC. The application converts the client side time to UTC and calls the procedure.

If no from date is sent in the input parameter then I have to default it to last Sunday.

Here's the current logic that I have

Declare @currentDate Date = getdate()
Declare @fromDate DateTime = Null

Set @fromDate = Coalesce(@fromDate, DATEADD(wk, DATEDIFF(wk, 0, @currentDate), -1))

Select @fromDate

This works well in most situations except a few:
If I'm a user in
--EST timezone,
--running the procedure at 10PM EST on 26-Feb-2022 and,
--I'm passing a null value for @from date.

The code above would be something like this:

Declare @currentDate Date = '27-Feb-2022 3:00:00' --UTC
Declare @fromDate DateTime = Null

Set @fromDate = Coalesce(@fromDate, DATEADD(wk, DATEDIFF(wk, 0, @currentDate), -1))

Select @fromDate

The result would be 27-Feb-2022 00:00:00 UTC which when translated to EST would be 26-Feb-2022 19:00:00. But since my date has to default to last Sunday it should technically be 20-Feb-2022 00:00:00 EST which is 20-Feb-2022 5:00:00 UTC. I'm very confused as to how to achieve this since I have to consider the client time zones when doing the calculations and it can be anything. I would appreciate some insights into how I can achieve this. Thank you!

Answer 1

You can do the conversion SQL side. If you change your declaration to:

Declare @currentDate Date = cast('27-Feb-2022 3:00:00' as datetime) 
    at time zone 'utc'
    at time zone 'eastern standard time';

everything should work as you expect. To talk through it, you're explicitly telling SQL that the date time is in the UTC TZ with the first at time zone and then converting it to EST with the second.