EST to IST

Question

Post reply

EST to IST

Rock

Old Hand

Points: 367
More actions
February 20, 2020 at 5:18 am

#3727248
I need to run a job in DB at every day morning 12:05 AM IST on daily basis. However, the DB instance runs in EST Zone . how do I convert EST time to IST Time and run this job?
- This topic was modified 4 years, 9 months ago by Rock.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Is the server in UTC or EST?

UTC and EST are not the same time zone

EST to IST is a 10hour 30minute difference, so if you want something run at 12:05AM IST, you set the schedule to run at 13:35PM

UTC to IST is a 5hour 30minute difference, so if you want it running at 12:05AM IST, you set the schedule to run at 18:35PM

There are plenty of time zone difference calculators out on the web, give it the source TZ and destination TZ and it will work it out for you

Thom A SSC Guru Points: 99130 More actions · Answer 2

Note, as well, that many places that use EST also observe DST, meaning that it's not always UTC-5, but sometimes UTC-4. If something like this is important, seems you should have your server in UTC.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

In order to protect against DST settings, just run the job 5 minutes past every hour. At the start of the job, check to see if it's the 'correct' time to execute.

You can get the current server time expressed in a differrent timezone using SYSDATETIMEOFFSET() and AT TIME ZONE:

-- Guessing 'IST' is 'India Standard Time'
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time' AS [FullISTDateTime], 
       convert(time, SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time') AS [ISTCurrentTime];

-- Results ---
FullISTDateTime                       ISTCurrentTime
----------------------------------    ----------------
2020-02-21 01:17:46.2964819 +05:30    01:17:46.2964819

Once you've determined the current IST time, just compare that to the time you wish the job to run. If there are more than a few minutes difference, then don't run the job.

DECLARE @TimeDiff int = (SELECT ABS(datediff(minute, convert(time, SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time'), convert(time, '00:05:00.000'))));

IF @TimeDiff > 5 
  -- more than 5 minutes difference; do nothing
  RETURN;
ELSE
  BEGIN
    -- do stuff;
  END;

-Eddie

Eddie Wuerch
MCM: SQL

Rock Old Hand Points: 367 More actions · Answer 4

I'm using 2012.

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time' AS [FullISTDateTime],

convert(time, SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time') AS [ISTCurrentTime];

This returns incorrect syntax near 'TIME'

Thom A SSC Guru Points: 99130 More actions · Answer 5

Rock wrote:

I'm using 2012.
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time' AS [FullISTDateTime],
convert(time, SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time') AS [ISTCurrentTime];
This returns incorrect syntax near 'TIME'

AT TIME ZONE was introduced in SQL Server 2016.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk