EST time calculation

sathish

8y878

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted 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]
0
sathish8y ago
Thank you so much Nitin ..

Now I am cleared and got the answer !
0
sathish8y ago
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 < EST midnight - Pass
Min and Max > EST midnight - Fail

Thanks
0
Nitin Sontakke8y ago
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?
+1
sathish8y ago
I have time like 2018-03-21 08:55:25.387 and 2018-03-21 22:17:26.288
0
Nitin Sontakke8y ago
And which time do you have?
0