EST time calculation

Question

Hi, Let me know how to find 1) Query to compare if time is prior to midnight EST 2) Query to compare if time is past to midnight EST Thanks

Nitin Sontakke · Answer

See if datediff of d (days) is 0 or not. As demonstrated below: declare @someTable table ( [id] integer ,[someDate] datetime not null ) insert into @someTable ([id], [someDate]) values (1, '2018-03-21 08:55:25.387' ) ,(1, '2018-03-21 22:17:26.288' ) ,(2, '2018-03-21 08:55:25.387' ) ,(2, '2018-03-22 00:01:26.288' ) select [id], min ([someDate]), max ([someDate]), case when datediff(d, min ([someDate]), max ([someDate])) = 0 then 'Y' else 'N' end [BeforeMidnight] from @someTable group by [id]

Nitin Sontakke · Answer

Sorry, what I mean to say is to convert a time to EST, which time do you have as in IST, GMT, which one? Time is time, unless you mention which it is, it is meaningless. Hope you get the point. Whichever time you have (GMT is the best) just apply the off set to it and you will get EST. Once you have EST, you will know if it before midnight or not. Also midnight is also a relative word. You should have date (as in day) to compare. Every time is obviously before mid night of that day, isn't it?

sathish · Answer

Thank you so much Nitin .. Now I am cleared and got the answer !

sathish · Answer

Nitin, Here my condition is If 2018-03-21 08:55:25.387-Min and 2018-03-21 22:17:26.288-Max (both are EST) are lessthan midnight of EST means Pass else fail. so how the sql query would be ? If Min and Max Min and Max > EST midnight - Fail Thanks

sathish · Answer

I have time like 2018-03-21 08:55:25.387 and 2018-03-21 22:17:26.288

Nitin Sontakke · Answer

And which time do you have?