SQL server Indian Timezone Date

Question

I'm using SQL server 2008 R2, (Located in US location)

I have a SQL Job to send email notifications to Customers. Based on their Birthday I'm getting the data from Database as below:

SELECT  CustomerId,
        CustomerName,
        Email,
        Dob,
FROM    Customers
WHERE   DAY(Dob) = DAY(GETDATE())
        AND MONTH(Dob) = MONTH(GETDATE())

And the JOB is scheduled at 9:30 AM IST.

Problem here is, since the Server is located in US and Job is running at morning 9:30 AM IST, It sends same email twice a day.

How can I handle such situation? It there any way to handle TimeZone? Please help me in this.

Answer 1

You can try below

   SELECT  CustomerId,
            CustomerName,
            Email,
            Dob,
    FROM    Customers
    WHERE   DAY(Dob) = DAY(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))
            AND MONTH(Dob) = MONTH(cast(switchoffset(todatetimeoffset(GETDATE(), '-06:00'), '+05:30') as datetime))