Change UTC to local time

yes that will do exactly what you want.

beware that if you are converting an stored UTC date that was saved in a country when they were on Daylight Savings Time, you may get the time off by one hour, so you should consider if at that time they where any DST at use in that country so you can adjust the calculation

>>so you should consider if at that time they where any DST at use in that country so you can adjust the calculation

I don't know how to change that script to do it.

the easiest way is to use the sql server 2016 method which involves the new AT TIME ZONE  option in sql server READ THIS AT TIME ZONE

I can't do that because not all clients have SQL 2016.


I'll go thru the link you sent me. I'm still not clear on how to do this.

Looking at the examples and these responses...sounds like there's no sure/correct answer/method to make sure UTC is converted to local time correctly.

thanks, David. Let me take a look.

Yes, the TZ implementation can vary from simple offset application to a complex code referenced by David... and nothing is 100% perfect for your needs.

Imagine just the date w/o time part. Should it be stored as UTC date or as local date in given TZ? The date could mean today for one user but yesterday for another user etc. etc.

And imagine user which would like to see the data from a different TZ in local time of such TZ... This can be e.g. remote doctor looking at patient therapy in a different TZ...

I would store everything in UTC and do all the calculations to local TZ at client side. Let SQL Server to store the nonambiguous data and delegate all calculations to clients. Users may decide whether to see UTC or certain TZ times...

>>I would store everything in UTC and do all the calculations to local TZ at client side

This is what we do now. Dates are stored as UTC in the database. When I display the date(C# code/Windows Forms) I want to display it in the client's local time.
In the stored proc, I want to change that UTC to the local time.

Let SQL Server do the data work and no calculations. Yes, it is easy to say…

Does the SP know the local TZ for the user? You may pass it as a parameter or evaluate it in a different way inside the SP and recalculate UTC to local TZ. I know such applications but this approach seems to be too complex namely when daylight saving must come into effect. OTOH, each client has enough CPU and knows the local TZ and to do the calculation at the client side is easier than on the server and it does not consume server resources.

>>Does the SP know the local TZ for the user?
 
This application is from 2002. It doesn't know anything :)

I don't understand the rest of your solution as far as passing in a parameter.

 I just want to display UTC in local time. Database has UTC. Display needs to be in local time. I'm hoping David's link is the answer.

as long as it is a datetime object in the database and you are retrieving it as a date time. you can use the to local time in .net

No need to worry about timezone and time change?

We're on .Net Framework 4.0 . Let me look at the link.

Yes, this sounds as a solution implemented on the client side. You don't need to update any SQL code.

you said not all clients have sql server 2016, but can you enable CLR on every client?

This is a C#/Windows Froms with .Net Framework 4.0. Very cold code. I can't change client's environment as far as installing or enabling things.

Does it mean the only way how to display local time is to calculate it in SPs on SQL server?

Does each user have separate login ID to which you may assign the time zone?

Does it mean the only way how to display local time is to calculate it in SPs on SQL server?

No, it doesn't mean that. I can change the code. I can't change any settings on client's environments.

Do forms submitted by users include date time values? or do you use the server to generate the date/time event in the DB?
Do user's choose/set what timezone they are in, or are you trying to determine in based on geolocation of the IP from which they connect, etc.

You are starting from the end, how do you determine what the difference should be? Does a user as part of their settings indicate which timezone they are in?

It's saved in the database. I think David's solution would work --> we have the UTC data saved. I just want to display it in local time.

make sure the data you send out, is not the data in the date/time fields that is used on the insert/update side.

make sure the data you send out, is not the data in the date/time fields that is used on the insert/update side.

Why? data is inserted into the column as UTC. I want to display as local time.

Just making a suggestion to make sure that the source of the date time that is inserted could not be modified by the adjustment you are considering.

i.e. the process, mechanism that extracts and adjusts, bleeds into the process that inserts/updates..

Thanks. stay safe and healthy wherever you guys are.