Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It seems that the DMV dm_broker_queue_monitors returns UTC time rather than local time. Why is it so?

I am wondering how to get local time from this view? I can get current time offset, but that is not valid for older dates (before daylight saving), so it is not really a correct way to do that.

share|improve this question

1 Answer 1

To get local time, your best bet is to use the CLR, if that is available to you. Matt Whitfield and Slawomir Brys have sample code you could use to convert which can handle Daylight Savings Time.

Regarding why the DMV is in UTC rather than local time, Service Broker can work across instances, so having service broker-related functionality all in UTC makes sense--that way, there isn't a problem with date synchronization between your east coast server and Tokyo branch. That'd be my best guess as to why that particular DMV is in UTC whereas a number of others (e.g., dm_exec_sessions) are in local time.

share|improve this answer
    
well, last_empty_rowset_time and last_activated_time- both happens on my instance, so i dont care about time on those computers who send "Receive" command (and UTC in this case is not addressing that anyway).. Thanks for proposed solutions. –  Jānis Nov 1 '12 at 12:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.