CODEWITHSUNDEEP
c#datetime
Alex
Alex

Reputation: 9720

Convert DateTime from UTC to user location timezone

In db I save DateTime in UTC like this:

CreatedDate = DateTime.UtcNow;

and also for every user I save timezone in this format "+0200", "-0300", "-0430"

now I want to filter results by date

SQL: SELECT * FROM tableName WHERE CreatedDate >= GETDATE()

how add value from timezone column to the selected dateTime?

Upvotes: 2

Views: 339

Answers (3)

ispiro
ispiro

Reputation: 27693

Something like this:

storedDate.AddHours(double.Parse(timezoneOffset)/100).ToString();

Untested!

EDIT

Or use something like this: (Again, untested)

TimeZoneInfo[] tz = TimeZoneInfo.GetSystemTimeZones().ToArray();
string t1 = tz.First(t => t.BaseUtcOffset == TimeSpan.FromHours(Math.Round((double.Parse(stored) / 100), 1))).Id;
DateTime time= TimeZoneInfo.ConvertTimeBySystemTimeZoneId(storedDate, t1);

Upvotes: 0

faby
faby

Reputation: 7556

you can do something similar to this

   DateTime utcDateTime = DateTime.UtcNow;
   TimeSpan offSet = TimeSpan.Parse("+01:00:00");
   DateTime checkDate = utcDateTime + offSet;

and then pass checkDate as parameter to the query

Upvotes: 1

Murray Foxcroft
Murray Foxcroft

Reputation: 13755

Depending on the language you are using, you can get this from the client application, e.g. for javascript. 

var offset = new Date().getTimezoneOffset();

Getting the client's timezone in JavaScript

This way you don't need to store it in the DB.

To add the time back in for your example, use parsing and DATEADD http://msdn.microsoft.com/en-gb/library/ms186819.aspx

Upvotes: 0

Related Questions