• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 926
  • Last Modified:

SQL date and time and different time zone

I have a .net application using SQL 2000 database. the application and DB is running on a server in US and therefore saving the time and date using US time. Being nearly one day in front of US time, I need to convert the dates to our time zone. In the database table the function getdate() is used for a date column. how can I modify it so I could save the date in the table using our time zone when data are submitted?

Thanks in advance

  • 2
1 Solution
Ted BouskillSenior Software DeveloperCommented:
You only have two choices for the current date.  GETDATE() and GETUTCDATE() which have to be converted into any other zones.  You can use DATEADD()

DATEADD(day, 1, GETDATE()) will add one day onto the current date.  You can use negative values as well.
Since the server is based on US..its good idea to store the values using US time zone. .. your application should there fore convert it to the required time where the user is based.
keeping a database server in US and then keeping a date field for another time zone is not a good idea. it will create more confusion for the new staff. ( unless u keep two different fields. one for us and one for ur)

in this case i think getutcdate() will help u as it will store a universal time zone on the database. and all applications will have to cnvert the data that is shown to users to the local time zone.

Ted BouskillSenior Software DeveloperCommented:
Wizilling makes a good point and something I forgot to add.  All systems I design store dates as UTC and then I convert to local time zones.  Even with web applications you can convert the time zone at the client side using JavaScript.  It eliminates a LOT of headaches.

Sadly I work at a large multi-national firm whose headquarters are in California.  So guess which time zone is the default? (SIGH) Yes PST.  So I often fix many bugs with date math issues due to some process misinterpreting the date time zone because the a developer assumed that the date was in their time zone.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now