CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Furqan Misarwala
Furqan Misarwala

Reputation: 1891

Time zone datetime comparison in sql server 2016

In the database I have timezone and date as :

Timezone 'India Standard Time'

DateTime '2018-07-19 15:47'

In Sql query I am comparing this with GETUTCDATE()

IF EXISTS( SELECT * FROM [dbo].[Notification] WHERE [Published] AT TIME ZONE  'India Standard Time'  <  GETUTCDATE())
  BEGIN
 PRINT 'True'
  END

This give me wrong result

I am new with this function so don't know exact way to compare it

Upvotes: 1

Views: 2478

Answers (2)

Ravi
Ravi

Reputation: 1172

just use

dateadd(MINUTE, 330, getutcdate())

in place of

getutcdate()

for IST

SELECT * FROM [dbo].[Notification] WHERE [Published]  <  dateadd(MINUTE, 330, getutcdate())

Upvotes: 1

Salvatore
Salvatore

Reputation: 1435

There are many approaches to get this working -

My approach is to convert your GETUTCDATE to IST so that you can run your query without any timezone issues.

SELECT * FROM [dbo].[Notification] 
WHERE [Published] < CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,GETUTCDATE()),’+05:30′))

Upvotes: 0

Related Questions