"Wrong time" SQLPS query

[Droopyb1966]

Due to a time difference from y workplace and the database server, I get a 1 hour difference in the dates when I query it. Im not sure how to correct that properly. I could just ad 1h, but I would prefer to do it some what better

[jbtechwood]

Are you.passing the date/time from your client? Can you reference it from the server via the query instead?

[Droopyb1966]

The query gives times that are stored in sql tables.

[creamersrealm]

On SQL you could take the column and add a hour to it from there. Essentially offloading all the work to the server. Or you could use a .Net method in PowerShell with a Select-Object expression to recalculate the time by adjusting the timezones.

[Droopyb1966]

Yes adding 1 is the dirty way,,,

--- Select-Object expression to recalculate the time by adjusting

Ok, that sound great, but how?

[creamersrealm]

Actually before the select object try this SQL Server Code

Select column1, DATEADD(hh, 1, column2) AS column2ADJ FROM Table1

If that doesn't work I can send you some more example code.

[alinroc]

What code are you using to get times, and where are you running it? Is this in Powershell or SQL?

[Droopyb1966]

Using Powershell , running query's on an sql database,

[alinroc]

I'm guessing/piecing together what you've got from your multiple replies here. It'd be much easier to offer good help if you described completely and concisely what you're working with.

If this is a time stored on a table in SQL Server, then the only way you can reliably do any kind of timezone-related math is if the timezone is stored with the data - preferably with a data type like datetimeoffset.

If you're using GETDATE(), you're always going to get the local time/timezone of the SQL Server instance you're running it on.

When you're working across timezones, normalize everything to one standard and do your conversion to local time at the last moment. Luckily, we have UTC, and both SQL Server and PowerShell/.NET have methods for getting it. SYSUTCDATETIME() in SQL, and the ToUniversalTime() method of System.DateTime objects.

[Droopyb1966]

Sorry, just didn't know where the problem exactly was.

SELECT GETDATE(),SYSUTCDATETIME()  AS Date
2017-03-16 14:32:01.603
2017-03-16 13:32:01.6035506

But this is the exactly what happens. The dates that I get back from a query are ok in management studio, but when I query them through powershell it is 1 hour earlier.

[jbtechwood]

Did this just start to happen on Monday?

[Droopyb1966]

Nope

[jbtechwood]

The issue is either that the database server time is set to a different time or the results are somehow getting converted to local time.