?
Solved

getdate to get proper date

Posted on 2007-12-06
9
Medium Priority
?
862 Views
Last Modified: 2008-02-01
I have a server which is located in the eastern time zone and all of my clients are in the atlantic time zone.  when i put in getdate() it gets the eastern date.  i need to be able to get the atlantic date.  is there a command which will show you the date/time where you are at?
0
Comment
Question by:Fraser_Admin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20419775
Not in sql server 2000/2005; you can get the GMT by using GETUTCDate()
0
 

Expert Comment

by:OFGemini
ID: 20419859
Use
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
function to get the result according to your format
Last parameter is about the required format
Get help in your T-SQL Help on Convert Function and look for the avaible formats.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20419930
Hello Fraser_Admin,

*If* your clients use the same DST rules as you, then you could use:

SELECT DATEADD(h, -1, GETDATE()) AS TimeInAtlanticZone

That will work except for when you get within an hour of the change-over from standard time to
daylight time, and vice versa.

Regards,

Patrick
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Fraser_Admin
ID: 20420403
that is what i ended up doing, but now we may have another client coming on board in the eastern time zone, so i somehow need to find out what time zone they are in so i can display the proper date for them.
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 20420585
I would suggest either using aneeshattingal's solution of using UTC, or pass the date/time from the client application to the server.
Is it possible to add another column to your table (TZO-Time Zone Offset)? In your client app (assuming VB, but can be done in most languages), code something like:

Dim tzo As Integer = DateDiff(DateInterval.Hour, DateTime.UtcNow, dateTime.Now)

And pass tzo to SQL Server. That way, you can use GETUTCDate() in SQL Server to store the time, and store tzo. That way, you can ALWAYS get the current local time by adding tzo to the datetime.
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 20420601
The good thing about the solution I present is that it will work for ANY time zone the client is in, Eastern, Central, Pacific, Hawaiian, or another country.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20420652
Fraser_Admin,

With the possibility of supporting multiple time zones, my simplistic suggestion no longer
merits consideration.  The other Experts are on a better track.

Regards,

Patrick
0
 

Author Comment

by:Fraser_Admin
ID: 20420688
yea i will have to put some thought into this.  the big thing i was looking for is say you have a user who is in one time zone and a user in another time zone and they are both running the same report.  i would like it to show them the current date in their time zone, not the time zone the server is sitting in.  maybe i will somehow have to assign time zones to a user and work that way.  just thought sqlserver could figure that out for you.
0
 
LVL 15

Accepted Solution

by:
dbbishop earned 2000 total points
ID: 20421076
SQL Server has no way of knowing what time zone you are in. Doing as I suggest (storing UTC and a time zone offest) is probably your best solution.

The good thing about this type of solution is, if I usually work out of the EST zone, but travel to a PST office and do some work there, you have not tied the user to a specific time zone.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question