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

SQL date and time and different time zone

Hi,
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

0
shmz
Asked:
shmz
  • 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.
0
 
WizillingCommented:
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.

0
 
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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