Converting a Datetime to UTC | Under the kover of business intelligence

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

正直的洋葱 · 携程酒店-索菲亚精选公寓(沈北汇置尚岛店)预 ...· 3 月前 ·

迷茫的警车 · Floating point ...· 1 年前 ·

严肃的跑步鞋 · Working with Oracle ...· 1 年前 ·

英俊的黑框眼镜 · Expert Profile: ...· 1 年前 ·

玩命的青蛙 · shell脚本之sed使用—-替换、变量、转 ...· 1 年前 ·

CONVERT(DATETIMEOFFSET -- assuming all servers are on CEST time ,@dt AT TIME ZONE 'Central European Standard Time' AT TIME ZONE 'UTC')

If you’re stuck on a lower version of SQL Server, I’d suggest you upgrade 🙂 Or maybe create a table with all the start and end dates of daylight savings time for each year, so you can easily look up the offset.

UPDATE:

Turns out the conversion to DATETIMEOFFSET isn’t even necessary, which makes the code even shorter:

DECLARE @dt DATETIME = '2020-02-22 22:23:13.920';
SELECT CONVERT(DATETIME,
			    @dt AT TIME ZONE 'Central European Standard Time'
				    AT TIME ZONE 'UTC');

Thanks to Adam for pointing it out!

------------------------------------------------ Do you like this blog post? You can thank me by buying me a beer 🙂

Thanks for this useful information. So the CONVERT is also not needed! No data types are being converted to or from DATETIME here 🙂 This is exactly the same, and simpler:

DECLARE @dt DATETIME = ‘2020-02-22 22:23:13.920’;
SELECT @dt AT TIME ZONE ‘Central European Standard Time’ AT TIME ZONE ‘UTC’;

Hello, Great post. We have a situation where we the timezone can be different for our clients. In that case how can we convert to UTC? is there a way around? Thanks in advance.

I guess in that case you’ll need to determine the timezone for the current user and pass it as a parameter?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Comment * </p><p class="comment-form-author"><label for="author">Name <span class="required">*</span></label> <input id="author" name="author" type="text" value="" size="30" maxlength="245" autocomplete="name" required=""/></p> <p class="comment-form-email"><label for="email">Email <span class="required">*</span></label> <input id="email" name="email" type="email" value="" size="30" maxlength="100" aria-describedby="email-notes" autocomplete="email" required=""/></p> <p class="comment-form-url"><label for="url">Website</label> <input id="url" name="url" type="url" value="" size="30" maxlength="200" autocomplete="url"/></p> <p class="comment-form-subscriptions"><label for="subscribe-reloaded"><input style="width:30px" type="checkbox" name="subscribe-reloaded" id="subscribe-reloaded" value="yes" checked="checked"/> Notify me of followup comments via e-mail. You can also <a href="https://sqlkover.com/comment-subscriptions/?srp=1472&srk=abb8101af3d74655b6e98fd9edfa0389&sra=s&srsrc=f">subscribe</a> without commenting. (No notifications? Check your spam filter)</label></p><p class="form-submit"><input name="submit" type="submit" id="submit" class="submit" value="Post Comment"/> <input type="hidden" name="comment_post_ID" value="1472" id="comment_post_ID"/> <p class="akismet_comment_form_privacy_notice">This site uses Akismet to reduce spam. <a href="https://akismet.com/privacy/" target="_blank" rel="nofollow noopener">Learn how your comment data is processed</a>.</p> <h2 class="screen-reader-text">Post navigation</h2> <div class="nav-links"><div class="nav-previous"><a href="https://sqlkover.com/optimize-for-unknown-for-inline-table-valued-functions/" rel="prev"><i class="fa fa-chevron-left"/> <span class="post-title">Optimize for Unknown for Inline Table-Valued Functions</span></a></div><div class="nav-next"><a href="https://sqlkover.com/exporting-environment-variables-out-of-the-ssis-catalog/" rel="next"><span class="post-title">Exporting Environment Variables out of the SSIS Catalog <i class="fa fa-chevron-right"/></span></a></div></div>