Obtain 00 hour of the current day, in UTC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Obtain 00 hour of the current day, in UTC

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-23 : 03:47:53

Hello all,
how can I obtain a datetime variable that is
the 00 hour of the actual day, converted in UTC?

For example, if now is '2013-10-23 08:10:00',
I have to obtain:

'2013-10-23 00:00:00'

and convert it in UTC:

'2013-10-22 22:00:00'

Luis

PS
I have to stay in SQL Server 2008 R2

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-10-23 : 05:19:10

1) >>For example, if now is '2013-10-23 08:10:00', I have to obtain: '2013-10-23 00:00:00'

To remove time portion from the datetime field
DECLARE @Date DATETIME = '2013-10-23 08:10:00'
SELECT DATEADD(DD, DATEDIFF(DD, 0,@date), 0)

2) Check the following script for converting Local to UTC time
DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()

-- convert local date to utc date
DECLARE @UTCDate DATETIME
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @LocalDate)

-- convert utc date to local date
DECLARE @LocalDate2 DATETIME
SET @LocalDate2 = DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), @UTCDate)

SELECT @LocalDate, @UTCDate, @LocalDate2

--
Chandu

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-23 : 08:49:04

Thank you very much Bandi.

Luis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources