How to verify ALL DateTime fields are converted into UTC format?

I've two databases. There are common Tables between them. In DB1, the datatime column are NOT in UTC fomat. The same tables in DB2 are in UTC format. I would like to ensure that ALL the DATATIME columns are in UTC fomat. Meaning that NONE of the DATETIME column value should be equal between DB1 and DB2 for the common tables.

How to achieve this? Please do assist.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jonnidipCommented:
What is the data-type of the fields?
Are them all "DateTime"? Or Varchar?

How can you distinguish "UTC format" and "Non-UTC format" from the following query?
select getutcdate(), getdate()

Open in new window


If you want to "convert" the "getdate" result, you will need also to know the time-zone where that date was generated.
My getdate now is: 2013-09-30 14:47:07.807, that means 2013-09-30 12:47:07.807 in UTC...

If we are on different time-zone, then our "getdate" will be different, but not the "getutcdate".
So, if you don't know the originating time-zone, you have no chance to know what the UTC date was...


Regards.
0
Easwaran ParamasivamAuthor Commented:
The datatype is DATETIME. I would like to TSQL query to ensure that DB1 database tables'
DateTime Fields are Different (NOT SAME  as because it is converted into UTC format)? How to achieve it?
0
jonnidipCommented:
I assume you have one and only one time-zone in the DB1 tables. This is because if the offset is 0, then the time can be the same as UTC.

If you are sure that the time-zone is always the same (and is not offset 0), then you can simply join your tables by your primary key and filter your records "where DB1.DateField <> DB2.DateField".
Is this what you need?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PortletPaulfreelancerCommented:
Are you saying that in DB2 the data type of one or more fields is datetimeoffset
but in DB1 the data type for the equivalent fields is datetime?

Q1 is there a data type difference?
Q2 Could you check the definitions of the table in both databases?
Q3 Would you mind running these small queries?
      you need to change the table names and provide a field where indicated:
SELECT top 10
        convert(varchar, [choose_a_field] ,126) AS mask_without_tz
      , convert(varchar, [choose_a_field] ,127) AS mask_with_tz
FROM [db1].your_table
;

SELECT top 10
        convert(varchar, [choose_a_field] ,126) AS mask_without_tz
      , convert(varchar, [choose_a_field] ,127) AS mask_with_tz
FROM [db2].your_table
;

USE DB1
SELECT
  /* display the timezone of the local server */
  DATENAME (TZoffset, SYSDATETIMEOFFSET())    AS tz_offset_db1
;

USE DB2
SELECT
  /* display the timezone of the local server */
  DATENAME (TZoffset, SYSDATETIMEOFFSET())   AS tz_offset_db2
;

Open in new window

---- and ---
You may find this msdn blog very useful reading, The Death of DateTime? it discusses the complexities of attempting conversion from datetime to datetimeoffset

There you will find this:
Convert from datetime to datetimeoffset (note that this uses the server’s current time zone offset, which could be inappropriate for historical dates):
SELECT TODATETIMEOFFSET (datetimevalue, DATENAME (TZoffset, SYSDATETIMEOFFSET()))
But you need to really understand that doing this retrospectively may be imperfect.
Please provide answers to the questions above, with the query results, before taking action to alter the data.
0
sachitjainCommented:
You could convert your non UTC datetime column values to their UTC equivalents by using following example

set nocount on
declare @dt table (Id int identity(1,1), DateEntered datetime)
insert into @dt values (getdate())
insert into @dt values (dateadd(day, 1, getdate()))
insert into @dt values (dateadd(day, -1, getdate()))

select * from @dt
update d set d.DateEntered = DATEADD(mi, DATEDIFF(mi, GETDATE(), GETUTCDATE()), d.DateEntered)
FROM @dt d
select * from @dt
0
PortletPaulfreelancerCommented:
except for the fact that we don't know where the transactions came from... and it does not account for daylight savings at all....

caution should be exercised in my view
0
Easwaran ParamasivamAuthor Commented:
Excellent!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.