2

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?

Alex
  • 8,908
  • 28
  • 103
  • 157

3 Answers3

1

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

faby
  • 7,394
  • 3
  • 27
  • 44
0

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

Community
  • 1
  • 1
Murray Foxcroft
  • 12,785
  • 7
  • 58
  • 86
0

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);
ispiro
  • 26,556
  • 38
  • 136
  • 291
  • Don't do that! That won't take into account special cases, such as daylight saving times. – Joanvo Jan 05 '15 at 13:21
  • @Joanvo Look at the question. Does it look like the OP _has_ enough information to check that? No. He didn't keep that information, and therefore that's impossible anyway. (Not all places in one time zone have the same dates for daylight saving times, or do they change the clocks at all.) – ispiro Jan 05 '15 at 13:24