datetime conversion from UDT/GMT to localtime

[Guest]

Hi,

Env:
VB .net 2005 against SQLServer 2000 SP4 on Windows XP (latest SP)

Situation: My database has all date/times stored in UDT/GMT in datetime
fields in sqlserver...(I'm in USA EST, thus, GMT now is +4.00) [When daylight
savings time goes away in the fall, it will be +5.00)

My VB 2005 .net GUI application searches for 'myData' between 2 points in
time, and I convert the query request from local time to GMT before I make
the query, the query then returns the correct resultset, but of course, the
date/times are in UDT/GMT

Question: Is there a way for me to have the datetime(s) in field(s) flagged
as UDT/GMT, or some Globalization setting, property, or class I can use to
help me always display datetimes that are flagged as UDT to display in
localtime?

Example: Bill, a client, orders a widget at 7AM est, it is logged in the
database as 11AM since est is +4.00 hours over GMT

Now, when Bob, a seller, wants to view a history of all the orders he took
in his office in NYC between 2 points in time, my gui app converts his from
and to datetime requests into GMT times and queries the database

My problem is that the resultset, although correct, still shows the data in
GMT/UDT time, and Bob needs to view the data in his localized time zone
(should subtract -4.00 hours)

Any hits,clues,or examples would be greatly appreciated

 

[William Vaughn]

I wrote an article that might help. See
http://www.developer.com/db/article.php/10920_3695251_1.
hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[WenYuan Wang [MSFT]]

Hello Georgejetson,
Thanks for William's great article. This way could be used for any timezone.

According to your description, it seems what you need is to convert the
Datatime from GMT to localized time zone. (If I misunderstood anything,
please correct me.)

Datetime.Parse method converts the specified string representation of a
date and time to its DateTime equivalent. We need to add "GMT" at the end
of string. Thereby, Parse method could recognize the GMT string.

Dim timeGMT As String = "8/27/2007 6:26:26 PM"
timeGMT += " GMT"
'Now,timeGMT is "8/27/2007 6:26:26 PM GMT".
Dim time As System.DateTime = System.DateTime.Parse(timeGMT)
System.Console.WriteLine(time)

Hope this helps. Please let me know if this is what you need. I'll follow
up. We are glad to assist you. Have a great day.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

Hello georgejetson,

This is Wen Yuan again. I haven't heard from you about a couple of days.
I just want to check if you have resolved the issue so far?
If the issue still persists, please don't hesitate to update here. We are
glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Walter Wang [MSFT]]

Hi georgejetson,

Have you seem above replies to your question? We're about to close this
post. Please feel free to let us know if you have any concerns or
questions. Thanks.


Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Thanks for all your help,

I ended up creating a userdefinedfunction that accepts as input a datetime,
and, returns the datetime back in localtime, all my views now use this
function for the clients

thanks again,

george jetson